Monday, August 1, 2011

Pulling SharePoint List using SSIS 2008

Do you want to pull SharePoint List data into SQL database using SSIS? If your answer is Yes then you can go for SharePoint List Source. You can download the setup from Microsoft SQL Server Community Sample: Integration Services: here is the link: SharePointListAdaptersSetup

This component has two controls - SharePoint List Source and SharePoint List Destination. The Specific Features of the included SSIS Components are:

SharePoint Source Component
  • Auto-detects all of the public fields from a SharePoint List or view of the list.
  • Supports removal of fields from the output flow, which optimizes the query against SharePoint for increased performance.
  • Supports restricting SharePoint data by a custom CAML query, which can be provided through a variable expression for fine tuned filtering over large lists.
  • Provides a tuning feature for the # of rows to return at a time. Often with SharePoint, large lists need to be brought over in smaller batches than wide (many field) lists, or else the Webservice will die.

SharePoint Destination Component

  • Auto-detects all of the editable fields from a SharePoint List
  • Only fields bound to columns from the input flow will be ‘updated’, for performance.
  • Supports deleting rows by ID

System Requirements
  • .Net 3.5 Framework
  • SQL Server Integration Services 2008

 How to Use
  • Download the sample from SharePointListAdaptersSetup
  • Run SharePointListAdaptersSetup.msi to install the SSIS component. This installer places pre-built binaries into the Global Assembly Cache (GAC) and into the C:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents directory (if present).
  • A good way to explore the sample is to try using it, understand its behavior, and then begin reviewing the source code for the component. If you do not see the SharePoint adapters in your toolbox automatically, then follow these instructions:
    • Open Business Intelligence Development Studio, open a package, and then click Choose Toolbox Items on the Tools menu.
    • In the Choose Toolbox Items dialog box, click the SSIS Data Flow Items tab, and then check SharePoint Destination and SharePoint Source.
    • Click Ok.
  • The SharePoint source and destination components should now appear in the toolbox for the data flow task. You can add the source and destination components to the data flow of the package.

Here are few FREE resources you may find helpful.