SharePoint List to SQL transfer - How to eliminate duplicate data?

Oct 11, 2012 at 3:07 PM

I have been asked to see if I can create a way to send a SharePoint list to an SQL table. So I downloaded and installed the SSIS SharePoint List Adapters for SQL Server Integration. Setup a new project in Visual Studio 2008.

I then setup the Data Flow where I have the SharePoint List as the source and OLE DB as destination. I mapped the columns I wanted. Made sure the data types match, etc...Ran the project and everything was fine. No errors or warnings. I saw the data in the SQL table. Then I ran the project again and it seems like everything gets duplicated. So initially SSIS sent 80 records from SharePoint to SQL then the second time I ran it it sent the same ones again so I now have 160 records in SQL.

Is there any way to only transfer new entries or ones that have been updated?

I tried searching but do not see that option anywhere.

This is my first time using VS2008 and SSIS.

Thanks

Nov 6, 2012 at 12:19 PM

I have the same exact scenario of yours - and successfully had my package setted up following the below guidelines. It may help you as well.

Considering the fact you would be using the Data Flow Task - which contains "SharePoint List Source" as the Source Component and Ado.Net Destination or Ole DB Destination as your destination component.

With this setup - create an additiona "Execute SQL Task" before the Data Flow Task Component - have a SQL Statement added to the Execute SQL Task to Truncate the Destination Table. So everytime - when your package is executed - as a first step - the destination table would get truncated.

As a second step - it fetches the complete data from Source : SP list and ports it to the Destination : SQL table.

Hope this helps.

Nov 6, 2012 at 4:25 PM

Thanks for the reply. That is what I ended up doing and it works.

I created an SQL Execution that runs before the data import. The execution runs a one line statement to clear out everything in the table before the import.

This works for what we are trying to do since we are just updating the SQL table with data from SharePoint and never make any changes directly through SQL.