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