SSIS SharePoint List Adapters

Sometimes even the easy things can be hard… It doesn’t matter how many times I attempt to explain how to use the SSIS Adapters available on Codeplex with source code and documented on MSDN I seem to fail. So this time I will include pictures… However, this post assumes you have read the SharePoint List Adapter Readme and the MSDN article..

This example uses two Calendars; Calendar is the source and Calendar.MSDN is the destination

After Installation and Configuration of VS2008 you should be able to complete the following:

1. Drag a SharePoint List Source & a SharePoint List Destination from your toolbox

2. Fill in the Properties of the SharePoint List Source and drag the green connector to the Destination

• 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 .asmxfile.
• ListName - The name of the SharePoint list, as seen on the SharePoint list page.
• ViewName - 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.

Example List Source SiteURL http://trex-si-11, ListName Calendar, ViewName Calendar

Example List Destination SiteURL http://trex-si-11, ListName Calendar.MSDN, ViewName Calendar

3. Next Map the Columns on the List Destination but do NOT Include ID if you want the Source to Create a Row on the Destination, Include an ID if you want the Source to Update the Destination..

The rules are:

• Create a Row (Modification Batch Type). A row is created on the server when you select this batch type and do NOT specify an ID value for a given row of data. Any columns that are not included receive the default values or a NULL value on the server.
• Update a Row (Modification Batch Type). A row is updated on the server when you select this batch type and You MUST specify the ID of an existing row. Only the columns contained in the data flow are modified. If you specify this batch type, but the data flow does not contain any updated rows, an error is raised.
• Delete a Row (Deletion Batch Type). A row is deleted on the server when you select this batch type and specify the ID of an existing row. If you specify this batch type, but the data flow does not contain any deleted rows, an error is raised.

Execute the Task and if its green your good to go. However, the rules above mean that you will probably need more than one Data Flow to handle the different scenarios

The MSDN Article By Kevin Idzi and the Readme should pretty much explain all you need to know so I have to wonder why I am explaining it again, for everyone who did not know the references…

-Ivan