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.
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.
Screenshots.
There is no direct way of extracting the SharePoint Excel data to SQL Server using SSIS. However, we can process multiple steps to achieve the result.
This post explains that how we can download the excel files from SharePoint and store it in a local drive and then process the stored excel from SSIS.
To download the files easily, we can use the tool called “SSIS Integration Toolkit for Microsoft SharePoint”.
Follow the below steps.
Screenshots.
A group container helps to have more than 2 data flows to be grouped in one container but does not have any properties for itself. Also there is no precedence constraint in or out to this group container, instead it needs to be used with the tasks itself.
To have this group, select more than 2 tasks and right click and select the group option. This also helps in hiding and showing the tasks within the group.
If you have any questions drop me an email on mani@yoursqlman.com