Import data from SharePoint Online | SansSQL

Saturday, November 15, 2014

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

4 comments:

Unknown said...

Thanks for Sharing.

There's another ref yet few months older but solid info
http://whitepages.unlimitedviz.com/2014/03/using-the-odata-source-connector-with-sharepoint-online-authentication/

Unknown said...

Good one, very informative.. thanks for sharing your views and ideas.. it is very useful to me.. thanks once again..


Java Training in chennai | Software Testing Training in chennai

Shanthi Cabs said...

Amazing article. Your blog helped me to improve myself in many ways thanks for sharing this kind of wonderful informative blogs in live. I have bookmarked more article from this website. Such a nice blog you are providing ! Kindly Visit Us @ Madurai Travels ! Best Travels in Madurai | Tours and Travels in Madurai

Egainz said...

best website designing company in Delhi

Post a Comment

Ads