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

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