Configure SharePoint Source

Since I don’t just want to dump data to a SharePoint list, but want to update existing records and add new ones, I need to retrieve the current contents of the list from SharePoint. The list I am trying to merge with is pretty simple:

You can pull SharePoint items using a SharePoint List Source object. Just drag one from the toolbox. Highlight the new object (click on it) and scroll down in the properties window to the SiteListName property. You’ll need to type in the full address in the SiteUrl and the display name of the list in the SiteListName. The SiteListViewName is optional (The default view is used if this isn’t specified). Also, be sure to set UseConnectManager to False. Here are my settings:

Now when you double-click your new SharePoint List Source you’ll have a Column Mappings tab where you can select the important fields to pull back from SharePoint (If you don’t have this tab, hit the Refresh button and verify your settings from above). Since all we care about is comparing the key fields, switch to the Input and Output Properties tab and select Remove Column for every column in the Ouput Columns except the ID and the SomeID columns (We need the SharePoint ID and the SQL ID). Your Column Mappings will now look like this:

 

Data Conversion

In order to determine which list items already existed, which are new and which ones to delete we need a common ID. The SharePoint ID will be used pretty extensively, but it’s the ID column from the SQL list, SomeID, that we are most interested in. We are going to join the results from SQL and the results from SharePoint into one dataset and then split them back out to do the deletions, additions, and updates needed.

To join them we’ll be using the SQL Identity column, SomeTableID. However, in order to join them correctly we’ll need them to be of the same datatype. Standard INT columns from SQL have their datatype set to four-byte signed integer [DT_I4] (You can find out the datatype by right-clicking the OLE DB Source and choosing Advanced Editor and checking the properties of the Output Columns). We used a number type column in SharePoint, which translates to a double-precision float [DT_R8].

To make things easier, switch to the Input and Output Properties tab of the SharePoint List Source editor window and choose the SomeID column in the Output Columns. Change the name of the Output Column from SomeID to SomeIDAsFloat:

Now add a Data Conversion object from the toolbox and connect the green output from the SharePoint List Source to it. Double-click it and check the box next to the SomeIDAsFloat column and switch the Output Alias to SomeID. Then match the Data Type to the SQL SomeTableID datatype (in our case it’s a four-byte signed integer [DT_I4]):

Required Sorts

In order to use a Merge object, both sources need to have their datasets sorted on the same column. So drag 2 Sort objects and connect the Data Conversion’s green output to one and the OLE DB Source’s green output to another.

In the sort for the SharePoint dataset, uncheck the pass through box for SomeIDAsFloat and check the sort box for the SomeID column:

In the SQL Sort check the box next to the ID column (in this case SomeTableID):

Merge Join

Now we’re ready to join the two datasets together. Drag a Merge Join object from the toolbox and connect the two sort’s green outputs too it. For this merge, it doesn’t matter which sort is the left or right join since we’re going to do a full join outer join.

Double-click the Merge Join object and set the Join type to Full outer join. Check the box next to all the columns from the SQL dataset and the box next to the SharePoint ID in the SharePoint dataset. Ensure the Join Key is set properly (In our case it’s the SomeID from SharePoint joined to the SomeTableID from SQL):

Split

Now that we have one big dataset, we can figure out which rows need to be added, deleted or updated. We can do all of this with a single Conditional Split object. Grab one and drag it from the toolbox and connect the Merge Join’s green output to it.

We know that any row where the SharePoint ID (SomeID) is NULL is a new record that needs to be added to SharePoint. Similarly we know that any row where a value from SQL (SomeColumn) besides the SQL ID is NULL is a record that no longer exists in SQL and should therefore be deleted from SharePoint. If it doesn’t fall into either category above, it is a record that was already added to SharePoint and simply needs to be updated.

Using that logic we create 3 outputs. The first output (Just click in the Output Name column) should be named New Records and the Condition set to ISNULL([ID]). The second output should be named Deleted Records and it’s condition set to ISNULL([SomeColumn]). Finally switch the default output name to Existing Records:

Add To SharePoint

Finally we are ready for our first SharePoint List Destination object. Grab one from the toolbox and drag it over. Connect the green arrow from the Conditional Split object and choose New Records in the Output dropdown. Set the SiteListName, SiteUrl and UseConnectionManager properties to the same as the SharePoint List Source object. Double-click the new SharePoint List Destination object and switch to the Column Mappings tab (If you don’t have this hit the Refresh Button and verify your settings).

With SharePoint List Destination objects, the default action is Modification. This can be used to either add records or update records. This is all dependent on you setting the ID column in the Column Mappings tab. If the ID is mapped then it is automatically an update, if not then it’s an insert.

For new records we want insert, so select <ignore> for the Input Column mapped to ID then map everything else accordingly. Especially important is mapping the SQL ID to the column storing that in SharePoint:

Update SharePoint

We’ll now do the exact same as above with the twist that we will be mapping the ID column. So add another SharePoint List Destination object and drag another green connection from the Conditional Split. This time pick the Existing Records in the Output dropdown. Set the SiteListName, SiteUrl and UseConnectionManager properties the same as before and double-click to open the advanced editor.

This time in the Column Mappings (again you might have to hit refresh if you’re missing this tab) map everything the same as our Record Addition output but keep ID mapped to ID:

Delete From SharePoint

To remove records from SharePoint, drag another SharePoint List Destination object and connect the Conditional Split’s green output arrow to it. You won’t have to select the output since Deleted Records is the only output left. Set the SiteListName, SiteUrl and UseConnectionManager properties the same as before. This time you will also need to change the Batch Type property from Modification to Deletion:

Double-click the Destination object and switch to the Column Mappings tab (again you might have to hit refresh if you’re missing this tab). The only thing you need to map here is the ID to ID:

That’s it. Attach this to a SQL job and schedule it at whatever frequency you need the thing to run and you’ve got a SharePoint list being synchronized with SQL through the power of SSIS. WOWEE!