I had a requirement to extract the data from SharePoint (only Metadata) and I was looking for a task to handle this in SSIS.
Then I found a simple 3rd party plugin to fulfil my requirement. In this post, I am explaining how to use the CData SSIS components.
You can download the component from https://www.cdata.com/drivers/sharepoint/ssis/.
The above link will take you directly to the download page. You need to buy this tool to use it on your production packages. If you want to test, then you can go with a trial version.
Download and install where you installed the SQL Server Data tools.
Once you installed then you can start creating an SSIS package using CData SharePoint Source and Destination tasks.
You need to create a connection manager to speak with on your SharePoint server either on-premises or online.
Right click on the connection manager section and choose “CDATA_SHAREPOINT”.
You need to enter your SharePoint site URL and credentials.
Once everything is done then drag and drop the “CData SharePoint Source” task. Point the created Connection manager. Under Table or View, you can choose any of the resources from SharePoint.
Check the columns and ensure everything is working. Then you can map the output to the destination tasks.