Search code examples
sqlsql-serversharepointssissharepoint-2007

SSIS doesn't update SharePoint list, duplicates data

I have created an SSIS package (see below) to import data from an external SQL query into a SharePoint 2007 list. The data imports fine but when the package is ran again to update the data it duplicates the records. I'm guessing that as there is no link between the SharePoint ID of the imported records and the data from my SQL query the routine has no idea what to update and just creates a new record. How do I prevent this and allow my data to be updated in the SharePoint list?

screenshot

Solution

  • If you are setting the key Id field in your SharePoint list target it will perform an update, otherwise the default is an insert. It sounds like you have not mapped the Id

    You can either

    • Set (map) the ID column thus forcing the SharePoint destination component to perform an update. Have a look at this example by Chris Kent

    • Limit your source select statement based on the last inserted record inside the SharePoint list. Prior to the data flow task, you would need to select the max(date or key?) from SharePoint and set an expression for your data source to include this value in the WHERE clause resulting in selecting only new records. This has the added benefit of limiting the amount of data traveling across the network and your existing insert setup would work.