Extract Data from SharePoint List using SSIS

ByHariharan Rajendran

Extract Data from SharePoint List using SSIS

Microsoft released a new task called “OData Source” which helps us to connect to online SharePoint and extract the lists.

Follow the below steps to configure and extract the data.

  1. Make sure to use the latest version of SQL Server Data Tools to work with SQL Server integration Projects.
  2. Use the Data Flow Task
  3. You can see “OData Source” task. Drag and drop into the data flow canvas.
  4. This OData Source task required a connection manager. Click New on the OData Connection Manager.
  5. You need to pass the below information to successfully connect with SharePoint Online.
    1. Service document location – https://<yoursharepointsitename>/_vti_bin/listdata.svc
    2. Authentication Type: Microsoft Online Services
    3. UserName: Your email address to connect SharePoint Online
    4. Password: your password.
  6. Test the connection.
  7. On the OData Source Editor, Choose the SharePoint list on the collection section.
  8. Check the columns and use any destination where you want to store the data.

Screenshots.

About the Author

Hariharan Rajendran author

Hariharan Rajendran is a Microsoft Certified Trainer and Lead BI Solutions Consultant with 8+ years of experience in Database, BI and Azure platforms. Hariharan is also an active community leader, speaker & organizer and leads the Microsoft PUG (Power BI User Group – Chennai), SQLPASS Power BI Local Group – Chennai and an active speaker in SQL Server Chennai User Group and also a leader in Data Awareness Program worldwide events. Hariharan also frequently blogs (www.dataap.org/blog), provides virtual training (on ad-hoc basis) on Microsoft Azure, Database Administration, Power BI and database development to worldwide clients/audience.

Comments Are Closed!!!