Use Integration Services to Import SharePoint List Items to SQL Server

A recent project I worked on called for data captured into a SharePoint list using InfoPath Forms Services to be ultimately imported into a SQL Server database for use with a set of performance reports. 

Given that InfoPath solutions can be designed to submit data to SQL Server (generally via a web service) you may ask why this wasn’t used for the solution.  Well, we wanted to do more stuff with the captured data in SharePoint, using workflows and task assignments before the data finally got approved and loaded into SQL Server.

In summary, the data was going to be submitted using InfoPath and Forms Services as promoted columns in a document library.  Once the appropriate people had reviewed, actioned and submitted the data it would be moved to a separate list, and any new items then needed to be loaded into SQL Server.  From there, Reporting Services would be used to generate and distribute nice looking reports.

So I took a look around to see what tools I might use to get the data into SQL Server with minimum coding effort, and ideally without installing any custom components on the SharePoint server.  I was aware of a set of SharePoint Designer Actions on CodePlex which included an Execute SQL Action.  For me the problem with this was that I was fairly sure I wouldn’t be allowed to install these on the production SharePoint server.

Also on CodePlex is a set of SQL Server Integration Services Community Samples, including SharePoint List Source and Destination Components.  For this project these components seemed more suitable, particularly as we would only need to import new items into SQL Server at the end of each day, and Integration Services would let us schedule a daily import.  And there’s even a MSDN article showing you how to do it.

To show how it works, I created a test SharePoint list called Responses which looks like this (let’s assume this data is coming in via InfoPath)

And on SQL Server, I created a table which looks like this:

Once the List Source and Destination components have been installed into Integration Services, I can create a Data Flow Task to read from SharePoint and write to SQL Server:

And map columns like so -

Of course when I run this I only want to import new rows.  The way I’ve handled this is to store the ID of the SharePoint list items in the SQL Server table and execute a SQL task to get the Max ID currently in the table like so –

SELECT ISNULL(MAX(ResponseID),0) AS LastResponseID  FROM Responses

As the SharePoint List Source component has a CAMLQuery property which I can update with a property expression at runtime, I can set it to something like this –

<Query><Where><Gt><FieldRef Name="ID" /><Value Type="Counter">1</Value></Gt></Where></Query>

In fact in the Integration Services Property Expression Editor I created an expression like this (LastResponseID is the output from my SQL Query):

"<Query><Where><Gt><FieldRef Name=’ID’ /><Value Type=’Counter’>" + (DT_STR, 4, 1252)  @[User::LastResponseID] +"</Value></Gt></Where></Query>"

So now if I put all of this together, I have an Integration Services Package which -

- Gets the last imported ID from the SQL Server table

- Queries the SharePoint list for items with an ID greater than the last one imported

- Loads any new list items into the table

Making it easy to get my list items -

Into my SQL Server table -

And I can schedule this to run each night, all without installing any new stuff on the SharePoint server or spending any money on new components!

Technorati tags: SharePoint, Integration Services

---

Possibly related posts: (automatically generated)

• Windows SharePoint Services Becomes SharePoint Foundation 2010
• Microsoft SQL Server 2005 SP2 Reporting Services integration with WSS 3.0 a…
• A Developers Guide to SQL Server Integration Services – BSDA Meeting for …
• Microsoft SQL Server 2005 Integration Services