Data import from SharePoint 2013 Online Office 365 using SSIS

It’s all about cloud, cloud now-a-days, every1 are going towards the cloud.

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.

First connecting to MS SharePoint Online using SSIS, system need the below components installed before stating.

Microsoft OData Source for Microsoft SQL Server 2012 Download here

SharePoint Server 2013 Client Components SDK Download here

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.

Download and Install both the components,

* Open the Visual Studio and create a new Integration Services Package
* Drag and Drop “Data Flow Task”
* In the “Data Flow Task” Drag and Drop “OData Source

oD source1





* Right-Click on “OData Source” and Click Edit
* 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”


















Click “All” and change the “Microsoft Online Services Authentication” to True

manager editor1
















* Click “Test Connection” its green then go head or error fix it and test again
* Click “OK”
* Then Choose “Collection” in “User Collection or resource path”
* In the “Collection” choose the list you want to import
* When you click on “Preview” you will be able to see the data from the list
* Now add the destination connection and run the package to import data from the SharePoint 2013 Online list.

Let me know if you have any further question and your comments will be learning point.

Microsoft Certified Solutions Associate (MCSA)