Accessing SharePoint Lists with SQL Server Integration Services SSIS 2005
Written By: Ray Barley -- 4/20/2009
-- read/post comments
-- print --
Rating:
(not rated yet)
Rate
Problem
I have a requirement to extract data from a SharePoint list. Ideally I would like to be able to do this from an SSIS package. In an earlier tip you showed how to do this by implementing a CLR function that invokes the SharePoint Lists web service. Isn't there a built-in component that we could use to do this?
Solution
There is a component available on the CodePlex site which should meet your requirements. The SharePoint List Source and Destination Sample provides a Source adapter to extract data from a SharePoint list and a Destination adapter to update data in a SharePoint list. In this tip we will walk through installing the CodePlex sample and creating an SSIS package to extract data from a SharePoint list using SSIS 2005.
Installation
Download the code for the SharePoint List Source and Destination Sample and launch the ,msi file to install the components. Note that there are separate downloads for SQL Server 2005 and SQL Server 2008. The components require the .NET Framework version 3.5; the installation will prompt you to download and install the .NET Framework if you do not have the version required. If you choose to download and install the .NET Framework your browser will open and navigate to the download page. As of the date of this writing you should be downloading and installing the .NET Framework version 3.5 Service Pack 1. There is a link on the page to download the full package; this will allow you to download everything you need. The default link will download a bootstrapper which will launch and download additional code as it runs. By downloading everything you have what you need to install the .NET Framework on another machine if necessary.
After installing the SharePoint List Source and Destination Sample code, open the Business Intelligence Development Studio (BIDS) and add the new components to the Toolbox under Data Flow Sources and Data Flow Destinations. Create a new SSIS package then click Tools on the top-level menu, then Choose Toolbox Items. Click the SSIS Data Flow Items tab and click the checkboxes for SharePoint List Destination and SharePoint List Source. After clicking OK on the dialog, you should now see SharePoint List Source under the Data Flow Sources and SharePoint List Destination under the Data Flow Destinations in the Toolbox. We are now ready to create a sample SSIS package that extracts data from a SharePoint list.
Create a Sample SSIS Package
Add a Data Flow component to the Control Flow of a new or existing SSIS package, then add the components below to the Data Flow:
The SharePoint List Source can be found in the Toolbox under the Data Flow Sources. To configure the component, right click on it and select Edit from the popup menu. You will see the dialog below:
 For this example you need to select a list in a SharePoint site. In my case I have a site called AdventureWorks that has a standard Contacts list that includes all of the employees from the DimEmployee table of the AdventureWorksDW database that comes with SQL Server 2005. After you pick a SharePoint list, fill in the SiteUrl and SiteListName as appropriate. The URL of my sample list is: http://bi-wss/adventureworks/Lists/Contacts/AllItems.aspx - use this as an example of how to extract the SiteListName and SiteUrl based on the URL of your list. Note that there is a SiteListViewName property where you can specify the name of a view that you have created for your list. The view allows you to specify the field list, sorting, etc. For our example we'll just leave it blank and go with the default view for the list.
As you can see in the above edit dialog for the SharePoint List Source there are two additional tabs - Column Mappings and Input and Output Properties. You can accept the default values.
For our example we'll write out the SharePoint list contents to a flat file. Add a Flat File Destination and configure its Flat File Connection Manger (named FlatFileOutput) to point to the file of your choice on your local hard drive; e.g. "c:\mssqltips\ssis_sharepointlists\contacts.txt". You may want to check "Overwrite data in the file" on the Flat File Destination's Connection Manager dialog so that the file will be overwritten if it already exists. In my case I set the column delimiter to Tab {t} on the Flat File Connection Manager's Columns page since my data has a FullName field with a comma separating the last name and first name.
We are now ready to run the sample SSIS package. Right click on it in the BIDS Solution Explorer and select Execute Package from the popup menu. You should see the package execute and write out the contents of your SharePoint list to a text file as specified in the FlatFileOutput connection manager.
One final note - you may have noticed the CamlQuery property on the SharePoint List Source Component Properties dialog shown above. CAML stands for Collaborative Application Markup Language. It is XML and in this case you could use it to specify sorting and filtering on your list. You can get more details on the CAML Query element here.
Next Steps
Readers Who Read This Tip Also Read
Free Live Webcast
Comment or Ask Questions About This Tip
|
