Seeking Guidance in Updating data in a SharePoint List Destination

May 3, 2012 at 12:32 PM

I have searched high and low looking for some guidance on updating a SharePoint List Destination from a SQL table and while I have come across many who have the same exact question, I cannot seem to find the answer. So I thought I would try here.

I have the following two scenarios:

1. Dump SharePoint data to an SQL table –

Using Microsoft SQL Server Integration Services (SSIS) and the SharePointListAdaptersSetup.msi from CodePlex, I can successfully take data from a SharePoint list and have it insert into a SQL table. Using a Lookup Data Flow Transformation, new records route to the OLE DB Destination via the Lookup No Match Output and updates route to the OLE DB Command (stored procedure call) via the Lookup Match Output. This works amazingly well so far.

                                                  [SharePoint List Source]

                                                                 |

                                             ____________[Lookup]_______________

                                            |                                               |

                            [OLE DB Destination]                      [OLE DB Command]

2. Have SQL data dump into a SharePoint List –

Here is where the trouble starts. If I use the same process but reverse the SharePoint List Source and the OLE DB Destination, things don’t work out. The Lookup only seems to connect to a DB and not my SharePoint List so when I run the package, there is always a match and I keep duplicating data.

If anybody can please guide me to the solution of inserting new data and updates into a SharePoint List Destination, I would be most appreciative and grateful.

Thank you.

-- John

 

May 3, 2012 at 3:26 PM

Eureka!

http://fsugeiger.blogspot.com/2010/01/synchronise-sql-table-with-sharepoint.html

May 3, 2012 at 8:58 PM

Not to spam my own list, but I am looking for assistance on how to update Lookup Columns in the SharePoint List Destination. I know it has something to do with IDs but I do not yet understand how that works in SSIS.

Thanks for any further assistance.