SSIS

CData SharePoint SSIS Components

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 3^rd 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.

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.

Extract Data from SharePoint excel using SSIS

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.

1. Visit – https://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-sharepoint/download
2. Download 64 bit of Toolkit
3. Install it on your system where you have installed the SQL Server Data Tools
4. Create a new SQL Server Integration Projects
5. Drag and Drop the data flow task into the control flow
6. You could see “SharePoint Source” and “SharePoint Destination” tools under Common folder.
7. Before Drag and drop the “SharePoint Source” task, you should create a SharePoint connection Manager.
8. Configure SharePoint Connection Manager for SharePoint on-premises or online.
9. Test the connection once.
10. Drag and drop the “SharePoint Source”. Point the created SharePoint connection and Choose the source list as “Documents” or the library where you have stored the excel files.
11. Configure the Download Destination.
12. Once excel files are downloaded then you can use that excel files to process further using excel source tasks.

Screenshots.

Group Container in SSIS

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