Import data from SharePoint Online

It's all cloud could and cloud now-a-days. Everyone are moving towards the cloud for making life easier.
In this post I will be explaining on how to extract/Import data from SharePoint Lists which is hosted on SharePoint Online of Office 365 using SSIS.
There is no direct way to connect to the SharePoint Online using SSIS.
For connecting to SharePoint Online using SSIS, we need the below components installed before stating.

1. Microsoft OData Source for Microsoft SQL Server 2012
   Download from http://www.microsoft.com/en-in/download/details.aspx?id=42280
2. SharePoint Server 2013 Client Components SDK
   Download from http://www.microsoft.com/en-in/download/details.aspx?id=35585 

Microsoft OData Source for Microsoft SQL Server 2012 is the SSIS component which we use as source connection to the Sharepoint Online Lists and SharePoint Server 2013 Client Components SDK allows us to connect to Sharepoint Online.

Once you download and Install both the components, 

1. Open the Visual Studio and create a new Integration Services Package
2. Drag and Drop "Data Flow Task"
3. In the "Data Flow Task" Drag and Drop "OData Source"
4. Right-Click on "OData Source" and Click Edit
5. Enter the "Connection Manager Name" and "Service Document Location" For Service Document Location, append "/_vti_bin/listdata.svc" to the SharePoint site
   For Office 365, we need to use "User this user name and password"
   
   
6. Click "All" and change the "Microsoft Online Services Authentication" to True
7. Click "Test Connection"
8. Click "OK"
9. Then Choose "Collection" in "User Collection or resource path"
10. In the "Collection" choose the list you want to import
11. When you click on "Preview..." you will be able to see the data from the list
12. Now add the destination connection and run the package to import data from the SharePoint Online list

Data source name not found and no default driver specified - SSIS Error

Sometimes you might get the below error when you are trying to run a SSIS package which is trying to connect to a 32 bit system from a 64 bit system/driver.

[DataReader Source [320]] Error: System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)

To fix this error:

1. Go to "Solution Explorer"
2. Right-Click on the Project and choose "Properties"
3. Expand "Configuration Properties" and choose "Debugging"
4. Set the "Run64BitRuntime" option to "False"
5. Click "ok" and save the project

Connect to SSIS service on machine failed: Error loading type library/DLL

When connecting to an SSIS instance on a newly installed system you might be sometimes presented with the error "Connect to SSIS service on machine <Machine Name> failed: Error loading type library/DLL"

Manage SSIS packages saved in multiple instances of SQL Server

Consider a situation where you have multiple instances of SQL Server installed on a single server and each instance has multiple SSIS packages store within the msdb.

Also we know that the SQL Server Integration services is not instance aware which means we cannot install more than one instance of SSIS on one server.

Now the question is how can we manage SSIS packages stored on the different SQL Server instances?

The Solution for this is very simple and can be achieved by modifying the SQL Server integration services configuration file to include the second instance. By default, the configuration file will include the SQL Server instance which was installed first and we have to add the additional SQL Server instances to this config file.
The name of the config file is "MsDtsSrvr.ini.xml" and will be usually located at "C:\Program Files\Microsoft SQL Server\100\DTS\Binn" and for SQL Server 2005 at "C:\Program Files\Microsoft SQL Server\90\DTS\Binn"

Before modifying the file

Now for this file, I will add one more SQL Server instance so that I can manage SSIS packages from 2 SQL Server instance with a single instance of integration services.
After modifying the file we need to restart the SQL Server integration server services in order to reflect the changes made.

Login timeout expired. The SQL Server instance specified in SSIS service configuration is not present or is not available

We all know that SQL Server Integration services is not cluster aware. This means that we cannot set the SQL Server Integration Services to failover automatically. But still in many situations we are required to have SSIS installed on a cluster and make it work when the database engine failover or failback.
To achieve this, we have to install SSIS on all the nodes of the cluster.
Okay, I have a 2 node cluster and I have installed SSIS on both the nodes.
What next?
When we install the SSIS, the configuration file by default refers to "." which means local default instance. But in the clustered environment, we never install the database engine with the node name and hence when we try to open the Integration services it fails with the below error message.

To fix this error, we have to modify the SSIS configuration file "MsDtsSrvr.ini.xml" to refer to the correct SQL Server Instance Name.
This file will be usually located at "C:\Program Files\Microsoft SQL Server\100\DTS\Binn" and for SQL Server 2005 at "C:\Program Files\Microsoft SQL Server\90\DTS\Binn"

Before editing the file

After editing the file, restart the SQL Server Integration services to refer to the new SQL Server instance name

Create and schedule SQL Server jobs with SSIS Packages

In my previous post, we have seen how to use SQL Server Import and export wizard to import or export data and save the package.
When you save the package that is generated using this wizard, it is actually an SSIS package.
You can also use Visual Studio to create complex SSIS packages which performs the data extraction, transformation, maintenance of database, cleanup, etc...

Looping through SQL Servers using SSIS or Dynamically build connection to multiple SQL Servers

Consider you are giver a task of gathering information from multiple servers. What are the different ways you can automate this?

1. Create linked servers in each server pointing to your central repository
2. Create Separate SSIS package in each servers which loads data to your central repository
3. Create one SSIS package with multiple data sources and duplicate the tasks for each data source.
4. Create one SSIS package which dynamically builds connection to each server and does the data loading tasks.

If you choose an option between 1 and 3 , then  you will have to do some extra work for gathering the server information when there are new servers added to your environment.

If you choose the 4th option then also you will have some extra work but it is very small and easy when compared to the first 3 options. Here you need to just add the new Server Names to the table and that is all and everything else will be taken care by the SSIS package.

In this post I am going to explain how to create a SSIS package which loops through multiple SQL server and gathers information  by building the connection to those servers dynamically.

1. Open “Microsoft Visual Studio”
2. Create a new “Integration Services Project”
3. Create a SSIS Package
4. Create 2 Connection Managers, 1 for the source (which will be set dynamically for each iteration of the loop)  and the other for destination (which will remain constant, in other terms your Central Repository)
   
   
5. Store All your Server Names in a table preferably in the Central Repository (Destination Connection)
6. Add 2 variables
    a. Name: ConnectionVariable (A name of your choice)
        Scope: Package
        Type: Object
        Value: System.Object
    b. Name: ServerName  (A name of your choice)
        Scope: Package
        Type: String
        Value: A Valid Server Name




7. Add an "Execute SQL Task" with SQL Statement like "select FullName from dba..tbl_ListOfServers".
   
   
8. Set the result set to Full Result Set.
   
   
9. On the Result Set page, Add a Result 
10. Set Result Name "0" and assign it to your Object variable (In our case it is “ConnectionVariable”).
    
11. Now add a “Foreach Loop Container” 
12. Connect it from the “Execute SQL Task”
13. Inside the “Foreach Loop Container” , add the required “Data Flow Task”
    
14. Now Right-Click on the “Foreach Loop Container” and click on “Edit”
15. Now Go to “Collection” page and Set “Enumerator” to “Foreach ADO Enumerator” and “Enumerator Configuration ” to the Object Variable(In Our Case “ConnectionVariable”) and Set “Enumeration Mode” to “Rows in the First Table”
    
16. Now go to the “Variable Mapping” page and Choose the “String Variable” (In our case it is “ServerName”) and set the Index=0
    
17. Now, in the “Data Flow Task” add a “OLE DB Source” (Dynamic Connection) and connect it down to a “OLE DB Destination” (which will be your Central Repository).
    
18. Now Select the Source Connection Manager (In Our Case it is “Source”) and Right-Click on this Source and choose “Properties”.
19. Expand the “Expressions” Option and click on the browse button (…)
20. Now in the “Property Expression Editor”, Choose the property “ServerName” and click on browse button (…). Now choose the String Variable (in our case “ServerName”) and Drag and Drop this variable into the “Expression” box and click “OK” and “OK”.

Now when the package runs, the “Execute SQL Task” will read the list of servers and stored in the table you specified and the “Foreach loop” will iterate over each record in that table, running the “Data Flow Task” each time while each time, the ServerName property of the Source Connection in the data flow will get a new value.

This article is also available in pdf format for downloading.
Please Click here to get your copy.

DTS packages in SQL Server 2005 and SQL Server 2008

As you all know that the DTS packages in SQL server 2000 is replaced by the giant called Integration Services which has more features than DTS, and DTS is no more supported in SQL Server 2005 and up.
To give a backward compatibility until the DTS packages are migrated(or converted by you) to SSIS packages, you can import the DTS packages into SQL Server 2005 and SQL Server 2008 and call them in the jobs to do their work.

These Imported packages will be present at the below location in SQL Server management Studio.
SQLServer>> Management>> Legacy>> Data Transformation Services.

The DTS packages under this path cannot be edited in SQL Server 2005 and SQL Server 2008.
For editing these packages, you need to install "DTS Designer Components"

If you want to edit DTS packages in SQL Server 2005 go here, download and install the component "SQLServer2005_DTS.msi"

If you want to edit DTS packages in SQL Server 2008 go here, download and install the component "SQLServer2005_BC.msi"

Once you install the DTS Designer Components, it will allow you to edit the DTS packages as you do in SQL Server 2000.
Just right-click on the package and choose the option "open" and this will open up the DTS designer for you.

I strongly recommend to convert the DTS packages to the SSIS packages as the support to DTS will be completed removed in future versions of SQL Server.

SQL 2005 SSIS and Excel 2007

Most of us know how to use an Office Excel file in an SSIS package. As long as we use an Excel file with the .xls extension it would be fine. But, when it comes to Office Excel 2007 it would be a bit different. The Connection manager for Excel files option that we usually choose will not work.

Since Office Excel 2007 and its .xlsx extension came after a long time after SQL Server 2005 release and since this new format is entirely different from the previous formats we have a little problem in using Connection manager for Excel files option with Excel 2007.This was sorted out with the Service Pack 2. This Service Pack gave us a new driver which could be used for Office Excel 2007 files.

Here are the brief steps on how to create a connection manager for Excel 2007.

In a new or existing package,

1. Add a New Connection and choose the connection manager type either ADO.NET or OLEDB by right-clicking on the Connection Managers tab.

2. Click on New and under the Provider drop-down list, select Microsoft Office 12.0 Access Database Engine OLE DB Provider

3. Click OK

4. Click on “All” which is located on the left side of the connection manager window, and type “Excel 12.0” against the Extended Properties.

5. Now go back to the Connections tab and type in the file path of the Excel 2007 file along with the file name there.

6. Click OK and you are done.
7. The same can be used for Access database 2007.