Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

FREE Webcast - Delivering Microsoft SQL Server High Availability on AWS
 

Using SSIS to Automatically Populate a SharePoint List


By:   |   Read Comments (15)   |   Related Tips: > Sharepoint

Problem

I wrote a query and I would like to display the results within a Sharepoint 2007 list. I know there are a few different ways to accomplish this, but I'd like to use a SSIS package so I can automate the data updates. Is this possible without creating a complex SSIS package?

Solution

In my opinion, the best (and easiest) way to accomplish this goal is using SSIS packages and a component from Codeplex called Sharepoint List Source and Destination. Ray Barley wrote a tip regarding this component a while back and he explained how to extract data from Sharepoint. I encourage everyone to check out his tip as it explains how to install the component and has some very good tips regarding the Sharepoint Source task. In this tip, we'll go over the Destination task in more detail.

Setup a Sharepoint List

For this tip, I'm just going to set up a quick custom list. This example is done in Sharepoint 2007, but Sharepoint 2010 should be close to the same.

Go to Site Actions, Create:

This example is done in Sharepoint 2007

Custom Lists, Custom List:

Custom List

For this example, I'll create a list called SQL Versions:

create a list called SQL Versions

Once I have a list created, I need to create a view with custom columns that match my SQL query. For this example I need Server Name, Instance Name, and Build. To create a view click Settings, Create View:

custom columns that match my SQL query

Choose Standard View:

Choose Standard View

Name the View (for this example, I'll name it SQLVersionView and make it my default view):

I'll name it SQLVersionView

Next we'll need to create custom columns. Go to Settings, List Settings:

Go to Settings, List Settings

To create the first column click Title and rename it to Server Name:

click Title
rename it to Server Name

To create additional columns click create column:

click create column

Add Instance Name (single line of text):

Add Instance Name

Repeat until all the columns are created:

Repeat until all the columns are created

Create SSIS Package

Now that the Sharepoint list is created we can move on to the SSIS package. Please read Ray Barley's tip to learn more about installing the Sharepoint Source List and Destination component.

First we'll need to create a Sharepoint Connection Manager. Right click in Connection Manager and choose New Connection.

Now that the Sharepoint list is created we can move on to the SSIS package

Scroll down and choose SPCRED and click Add:

 choose SPCRED and click Add

From the Control Flow we will add our Data Flow Task and right click to configure:

add our Data Flow Task

Add the DB source and Sharepoint List Destination tasks to the designer (For this example I'm using the following table for my DB source):

Add the DB source and Sharepoint List Destination tasks to the designer

Configure DB Source:

Configure DB Source

To configure the Sharepoint List Destination right click and edit. Choose the connection manager you created earlier:

To configure the Sharepoint List Destination right click and edit

Click the Component Properties tab and change the SiteListName, SiteListViewName, and SiteURL name:

  • SiteListName: The name of the SharePoint list, as seen on the SharePoint page.
  • SiteListViewName: The name of the SharePoint list view from which you want to retrieve data. You can use a list view to pre-filter rows and eliminate the need to add dynamic CAML queries to your package. This does NOT affect columns, only data. If you do not specify a list view name, then the default list view is used. The default list view is the one that appears first in the View dropdown list, which may not show all of the items in the list. Leaving this option blank is the same as entering the default list view, which is typically All Items if the default has not been changed.
  • SiteURL: The URL for the primary site on which the list is found. Do not include any other subfolders or list paths, or the location of an .asmx file.
The URL for the primary site on which the list is found

Click the Column Mappings tab and make sure all the columns are mapped correctly (delete mapping between ID's for this example):

Click the Column Mappings tab

Run the package and if everything is setup correctly your Sharepoint list should have data in it:

Run the package and if everything is setup correctly your Sharepoint list should have data in it

Run the Package as a Job

To run this package as a daily job that will repopulate the Sharepoint list hourly, daily, weekly, etc. we'll need to create a Sharepoint Source and another Sharepoint Destination task. The Sharepoint Destination task will be similar to the first task we created above except we will be using the DeletionĚ batch type.

From the Control Flow we will add another Data Flow Task above the one we created above and add a precedence constraint.

From the Control Flow we will add another Data Flow Task

Right click the new Data Flow Task and choose Edit. In the data flow view, add the Sharepoint List Source and the Sharepoint List Destination:

Right click the new Data Flow Task and choose Edit

To configure the Sharepoint List Source, right click the task and choose edit. Choose the appropriate Connection Manager:

Choose the appropriate Connection Manager

Click the Component Properties tab and change the SiteListName, SiteListViewName, and SiteURL name (these should be the same entries as the Sharepoint Source task we created earlier):

Click the Component Properties tab

Once the Sharepoint List Source is configured, drag the precedence constraint/arrow down to the destination. Now, it's time to configure the destination. The Sharepoint List Destination will be exactly like the task we created in the first Data Flow task except we will change the Batch Type to Deletion:

Once the Sharepoint List Source is configured, drag the precedence constraint/arrow down to the destination

Now when you run the package it will basically delete all data from the Sharepoint list and repopulate it on a schedule that you can define within a SQL Agent job.

In the next tip, I'll show you how to create a KPI in Sharepoint based on this information.

Next Steps
  • Take a look at Ray Barley's tip to see what else you can do with the Sharepoint List Source and Destination component
  • Download the component from Codeplex here
  • Check out more of MSSQLTips Sharepoint tips here


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Brady Upton Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, June 14, 2017 - 8:36:38 AM - Rick Back To Top

 

VS 2008 BIDS:  Attempting to use the SharePoint List Source:  Creating the connection manager I get this error:

 The connection manager user interface specified by type name 'Microsoft.Samples.SqlServer.SSIS.SharePointListConnectionManager.CredentialConnectionManagerUI, SharePointListConnectionManager, Version=1.0.0.0, Culture=neutral,PublicKeyToken=f4b3011e1ece9d47' could not be loaded. (Microsoft.DataTransformationServices.Design)

Do you know what  the corrective course of action is to complete my SSIS package that retrieves data from a SharePoint site?

 


Wednesday, August 17, 2016 - 12:32:17 PM - Luis Back To Top

 

 Any idea on how to write to a specific folder in a list?


Thursday, March 03, 2016 - 4:03:48 PM - Mike Back To Top

 Hi Brady,

 

Does this component work with SharePoint Online as the target destination?

 


Sunday, January 24, 2016 - 11:52:41 PM - denisse Back To Top

 

 

Hi, I would like to know what version of VS is used here. I have VS2013 and VS2015. I am unable to view the SSIS Data Flow Toolbox after installing the adapters. 

Even if I can view the SSIS Toolbox, the SharePoint components are not there. 

Should I install a lower version of VS? 

Thank you


Thursday, July 09, 2015 - 2:41:40 AM - Byock Back To Top

Hi Brady,

 

I curious also to your input on Bobby's concern. Have you answered it?

 

Regards


Monday, August 04, 2014 - 12:53:43 PM - Bobby Back To Top

Brady,

Thank you so much for this.  I only have a few concerns with this solution, and reminiscent of my past experience with SharePoint Lists and syncing data, I will provide a fair warning:

Because this one-way sync requires prior deletion of all records to work as intended, it directly involves the SharePoint Recycle Bin.  If the Recycle Bin is not purged or maintained during jobs like these, it could very well be a problem down the road, becoming overpopulated and hard to maintain, which is what I've experienced in the past.

If we quantify this practice in a more extreme scenario, one where you would be populating the Recycle Bin with the records deleted from maybe several Lists and at perhaps varying intervals (live, hourly, nightly), you may run into the unfortunate occurrence where the Recycle Bin Clean-up Timer Job times out and cannot continue a batch.  We experienced this during heavy utilization of jobs similar to this.

So if you have a multitude of Lists that are clearing data regularly; live, hourly, or daily even with lots of records dumped, be cognizant of the load on the Recycle Bin and maybe author a purge on the Recycle Bin as part of the process. 

We had over 16 million records in the Recycle Bin in our 2007 farm at one point and that became a major issue for us with obvious performance impact and downtime.  This was likely due to the job not being able to complete a batch.  I'm not even sure if the Recycle Bin procedure has improved during 2007 Service Patches and in 2010, but I'm very careful now about deleting records in bulk pushes and syncs.

I'm curious what you think of the above and what your SSIS package would look like with this in mind.

Cheers to you and your efforts in sharing this valued knowledge.

--Bobby


Thursday, June 26, 2014 - 4:55:53 AM - denisse Back To Top

Thanks for this. It's exactly what I need. But I'm currently having a problem executing it. I'm encountering the error "Destination components do not support outputs."

Why is that?

Thanks again!


Wednesday, April 23, 2014 - 9:52:08 PM - Brodie Back To Top

Thanks for the post Brady, however often the SP destination lists would be used as lookup lists wouldn’t they? (Which is what I am currently trying to do). In that case, you couldn’t use this method because any lists that referenced the SSIS-populated list would lose their data every time the destination list is refreshed.


Friday, April 11, 2014 - 4:36:22 PM - Al Back To Top

Can you provide the steps to delete all data from the SharePoint list and repopulate it on a schedule using a defined SQL Agen job? 


Monday, December 23, 2013 - 5:13:34 AM - Effie Back To Top

Thanks for a great step by step, working like a charm!

 


Wednesday, December 11, 2013 - 4:56:11 AM - upendra Back To Top

Hi,

I am unable to insert a value to sharepoint list cloumn with the type "person or group" from the sql server table. Need to know that wether we can insert or not..if we insert please let me know the solution

 


Wednesday, September 18, 2013 - 5:37:54 PM - David Back To Top

Hi Brady,

Thanks for the article on Auto Populate SP list.  I am new to sp and not sure what to look for or what to tell administrator to look for when I get an error message trying to connect to Sharepoint server.

Here is the message:

Error at Data Flow Task [SharePoint List Destination]: System.ServiceModel.EndpointNotFoundException: Could not connect to https://xxxxxx. TCP error code 10060: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connect host has failed to response. 

Any suggestions?

THANKS!

 


Thursday, May 16, 2013 - 9:54:24 AM - Nicole Willis Back To Top

THANKS for that article!  This was exactly what I was looking for after days and days of searching different articles. The refreshing the data part of the article where you delete all the list items is perfect! No article I found was ever this easy or straightforward.  THANKS again.


Tuesday, February 05, 2013 - 6:26:37 AM - Aravind Back To Top

This is what i was looking infor it worked for me Thank you very much :)


Tuesday, January 29, 2013 - 5:31:46 AM - Sander Hilgerink Back To Top

Hello Brady,

 

thanks for this info, It really helped me a lot to set up a connection between SQL Server and a SP list. However, I'm now lokking for a similar kind of connection, to just add new records. In your example, you just delete all records and repo***te the whole list. That won't work for me, since I add several extra columsn where users can put their own additional information. That information would be lost in this case. Is there a way to let SSIS just insert records from the source that don't already exists in the destination list?

 


Learn more about SQL Server tools