1/21/2015

SharePoint List Source and Destination In Action

For my latest assignment I was asked to pull and write data to SharePoint List into SQL Server.

First thing, I did a web search.  Found a free site containing components for SharePoint Lists:

http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652

It appears to have the blessing from Microsoft so it's legit.  However, after installing in Visual Studio 2013, after installing the SQL Server Data Tools, the components did not appear in the SSIS Toolbar.  I tried installing the DLL into the GAC to no avail.

After researching, found this article, saying the Visual Studio 2013 works with SQL Server 2014:

https://msdn.microsoft.com/en-us/hh297027.aspx

So installed Visual Studio 2012 and SSDT, installed the SharePoint components and sure enough they appeared in the Data Flow of the SSIS toolbar:



So I created a new SharePoint connection, dropped the SharePoint List source component onto a Data Flow canvas, entered the SharePoint URL, name of the List, mapped the fields:



created a destination table in SQL Server, ran and presto, it ran first time with no errors. 


How often does that happen?

And the data appeared in the SQL Table:

 

And to do Add, Modifications and Deletes, you simply reverse the order of the components from SQL Server table to SharePoint.


To do a Modification, you change the type to Modification:

 and match the ID Key:

To Add, remove the link between the ID fields:
 
Run the SSIS Package and you'll see your new entries listed in the SharePoint page:

And to Delete, keep the ID fields matched, and change the Type to Deletion:

So that's an easy introduction to SharePoint components for Pull and Pushing data from SQL Server.

Hope you enjoyed~!


1 comment:

  1. if removing "ID" --> ERROR: you must map a column from the input for the ID output column if deleting data. help!

    ReplyDelete

Get Sh#t Done!