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 image

image

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

imageimage

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..

imageimage

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

image

 

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

Technorati Tags: ,,

3 thoughts on “SSIS SharePoint List Adapters”

  1. With above info. all the data is exported into Sharepoint column, except the Document data which is in Binary format in SQL Server.!

    What must be the SharePoint 2010 Column datatype to receive the binary data from SQL Server via SSIS?

    Thanks.

  2. Plz tell me that how can i import excel data in sharepoint list using ssis.?
    sharepoint list adapter is only feasible to add sorce and destination tool in visual stdio 2008.
    For visual stdio 2013 please suggest me how can i use sharepoint list adapter and further continue.

    Is sharepoint list is paid or free of cost?is it only compatible for vs 2008 or all version?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>