Help updating records in a sharepoint list using SSIS?

• bleach

  SSC-Addicted

  Points: 421

  More actions

  March 2, 2011 at 10:53 am

  #388951

  Hi All,

  First, I am using SSIS in SQL Server 2008, but I could not find the appropriate forum.

  I am somewhat new to SSIS and I am struggling to figure out the best way to approach a problem.

  Background:

  I have an SSIS package which runs weekly to import new data from our accounting database into a sharepoint list. I am using a merge join with a left outer join to only bring in rows which are not already found in the sharepoint list. I am not updating any existing rows. This is all working well. The sharepoint list currently has about 5,000 rows in it.

  Problem:

  I have a new requirement, which is to bring over an additional column into the sharepoint list. This is fine for new records, but I also need to update all the existing records with the value in this column. I can't just replace all the data in the sharepoint list with new data, because this breaks the lookups that have been built on this data within the sharepoint site. I've considered the following solutions:

  1. Using an execute SQL task to update the sharepoint list by querying the Sharepoint database directly. This is probably what I would do, however, my understanding is that it breaks supportability with Microsoft to update the sharepoint database with a SQL query.

  2. Using a Lookup or using a merge join. Hopefully I am missing something but I don't see how to update the records in the sharepoint list without deleting and repopulating the list (which as I mentioned before is not an option.)

  I am hoping that there is an easy way I might achieve this in SSIS. Any suggestions or thoughts are welcome.

  Thanks!

  brl

• hrengifo

  SSC Enthusiast

  Points: 101

  More actions

  January 13, 2012 at 1:21 pm

  #1433494

  Hi, I am actually in the same situation, did you get result of this issue.

  Thanks in advance for your answer.