UPSERT into SP list

Jun 9, 2010 at 9:18 PM

Hi,

I'm using SP2010 and SQL Server 200l8 R2.  have successfully created a SSIS data flow that pulls from my SQL database and inserts into a SP list.  Now I want it to insert records that don't exist in the SP List and update the records that do.  The SP list has a column called external_id which maps to my SQL database's ID, so I can detect whether the row I'm inserting into the SP list is new or needs to be updated.  I've come up with this method:

Use a lookup transformation to find out if the row exists in the SP list, and if so update, if not insert

PROBLEM: I don't know how to connect a SP list to the lookup transformation because the SSIS source doesn't use a connection manager, and the lookup asks for an  OLE DB connection manager.  What am I missing here?

I saw that the beta version of this project has a connection manager, but I want to use the most stable components that are available.

Thanks in advance for any ideas!

Coordinator
Jun 10, 2010 at 5:00 AM

OK, there are various ways to do this.

First of all, you can't use an OLEDB connection manager to get to SharePoint.

So, lets go over the various ways.

1) You can use the Cache manager in a dataflow, use the sharepoint source and load data you need into the cache manager, which you can then attach to the lookup component. That would be pretty slick (haven't done that), but would do what you need.

2) In the same dataflow, you could add a SP source and load the data in - and then Full merge to your SQL. The resulting dataset would have Null for the id in the SQL where the Sharepoint rows are old, Null for the id in the SP where the sql row is new, and null in neither when it might be an update (i say update cause the data might be the same). A conditional component to check the fields of interest looking for 'differences' would cut down on your update rows to just those that are different. This is how I tend to do it - but either way would do the job.

NOTE: Make sure when pulling from sql, you ISNULL(..,'') so that SSIS does not have nulls in the dataflow. Or else your conditional to check if a value in sql vs sharepoint will have to use the ISNULL function and it gets super messy. Better off just writing the sql to get rid of nulls to make it just string comparisons.

So those are two different variances.

The connection manager has nothing to do with your scenario. The beta version has a connection manager to store the credentials of the user in which to make the call to SharePoint under. That's it.

Jun 10, 2010 at 10:15 PM
Hi kevinidzi, Thanks so much for your reply. I'd like to attempt method #2, but what I cant get my head around is how to select the SP source's data (the one I added to the flow). I'm thinking it needs to be something like ~ get a row where the id = (id of the other datasource row). I'm not sure how to implement this, or do I have it conceptually wrong. Sorry, I'm a bit new to SSIS. Thanks
Coordinator
Jun 11, 2010 at 7:11 AM
Edited Jun 11, 2010 at 7:17 AM
Concept is wrong :) No problem, So make a source component, just select everything from the list in this case. So you'll have a dataflow with all of the existing rows. Now add a Full merge component with the SQL on one side and the SharePoint source on the other, and the join column will need to be the ID. Note: You'll have to add a SORT component after the sql and one after the sharepoint to sort by the ID value to make this work (there are shortcuts, but this is easiest for right now) After you have the merge component bring the data together, then you use the conditional filter to do the other items as listed. Some examples: http://www.mssqltips.com/tip.asp?tip=1322
Jun 11, 2010 at 5:34 PM

Thanks SO MUCH!!  That worked perfectly, plus learning that join technique will triple what i can do with SSIS.  I didnt bother with the checking for differences conditional because this package will only have to run twice in our production environment.

 

Thanks again!

Coordinator
Jun 12, 2010 at 2:30 AM

Glad it worked! That is a common scenario I think people don't realize you can do fairly easily with SSIS.

Aug 23, 2010 at 2:32 AM

Hi,

This is working very well - we are using the merge join and conditional split to do the upsert - had one minor issue with the SSIS package updating existing list data though. After already running successfully for a while, the sort order on the destination list was changed from the ListID to the item name (string). The SSIS import then stopped recognising a couple of list entries as already existing, so it created duplicates. Changed the sort order back to the ListID and it works fine.

Thanks