Pre-Requisite:
Since the Sharepoint List related SSIS components are not available in the standard SSIS toolset one has to add these dataflow components to the toolbox by adding a reference (a DLL) available at http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652 (Please consider the Beta version as it is the latest one).
Note: we have two sets of SharePointListAdaptersSetup.msi, one for 2005 and the other for 2008 and above versions.
How to Configure?
Once after downloading the (.msi) file. Run the executable and provide the installation path where the rest of the (.dll’s) are placed (which is nothing but the directory you used for installing the BI Components while installing SQL Server) because the editor fails to identify the components if they are installed to some other directory.
If you are not aware of the directory details, prior running the .msi, open one of your package in your SSIS project, Open Dataflow task, Right click on the tool box and select Choose Items…
Path gives you the absolute path info where the rest of the SSIS components are installed. Use the same path as the destination for your .msi. After proper installation your SharePoint list components appears in the list.
How to Read the Data from Sharepoint List:
1. Create a new connection under connection managers. Provide proper credentials.
2. Open the SharePoint List Source and select the connection you have just created.
3. Under Component Properties provide the CamlQuery, SiteListName & SiteURL.
4. Map the columns using column Mappings tab.
How to Write the Data to Sharepoint List:
1. Steps 1 & 2 (creation of new connection and using it) are same as above.
2. Select Batch Type as Modification (for Insertion & Updation) & Deletion for Deletes. Provide the SiteListName & SiteURL.
3. Map the columns using column Mappings tab. Ignore ID attribute mapping if it is an insertion case. Use it only for Updation Scenario.
