The WorkerThread Blog

we know stuff so you don't have to

Use Integration Services to Import SharePoint List Items to SQL Server

Posted by workerthread on August 28, 2009

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)

spResponse

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

SQLResponse

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:

Dataflow

And map columns like so –

Mappings

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 –

ResponseList

Into my SQL Server table –

ResponseData

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!

Advertisements

18 Responses to “Use Integration Services to Import SharePoint List Items to SQL Server”

  1. […] Use Integration Services to Import SharePoint List Items to SQL Server (Worker Thread Blog)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. […]

  2. SharePoint Development Weekly Roundup (01Sep)…

    TOP THIS WEEK Deployment Survey
    If you haven’t alr ……

  3. Gil said

    Regarding the “Use Integration Services to Import SharePoint List Items to SQL Server”, how might you handle updates to the list items so that they reflect in the SQL DB? Also, what about deletes? This sounds great for end of life list items to go into sql, but if I can’t see the list items as they update in sharepoint also update in SQL then it’s not so good for me. Might you have a solution for this?

    • Hi Gil

      As you say my requirement was for SQL Server to be a repository for completed SharePoint items. But that doesn’t mean you couldn’t use other transformation components of Integration Services to update changed records or capture deletions. Best bet would be to take a look at some of these components, such as LOOKUP and CONDITIONAL SPLIT. There are lots of blog articles covering these, here are a couple of examples –

      Get all from Table A that isn’t in Table B

      Checking if a Row Exists and if it does Has it Changed

      I would think carefully though about data which belongs in SharePoint and data which is obviously relational and should be in the SQL Server DB in the first place.

      Derek

  4. jagan said

    hi..gril,
    nice stuff.but here my requirement is i need to run this periodically .means i need to be run end of the day .can u suggest me how to do it.its very urgent.

    Thanks for your efford…
    jagan

    • Integration Services packages can be deployed and then run as scheduled jobs with SQL Agent. Do a Google or Bing search for SSIS deployment or SSIS schedule for lots of articles on how to do this.

      Derek

  5. Great post, Derek.

    Those SharePoint adapters are very useful and need to be promoted as a way to make SharePoint more dynamic! Thanks for laying out how to use them in a walkthrough fashion.

    I’d like to plug a site on CodePlex that lists this and a ton of other great extensions for SSIS – the Community Tasks and Components project (http://ssisctc.codeplex.com). If you need SSIS to do something, chances are that someone else needed that too and coded an extension to help – just like the SharePoint list adapters…

  6. Boris said

    Hi, thanks for the great post.
    But I wonder if this works.
    For me, I have some data in a MOSS which also has some numbers. When I use the list adapters, I have to convert the data to int as they come as double.
    In your post it looks like you can directly load the data into the destination.
    I also have the problem, that all of my numbers (in MOSS defined as number) are filled with zeroes behind(!) the actual number. So 160 becomes 16000000000000.
    Did you encounter some one of these problems?
    Greetings from Germany
    Boris

    • Hi Boris,

      No, I definitely did not see this problem. I was loading integer numbers int SQL Server INT columns and it worked fine for me using SQL Server 2008 with SP1.

      I guess if you are getting a problem you could use Integration Services to further transform the numbers when they are loaded?

      Also, if you have a repeatable bug I would definitely recommend reporting the issue on the CodePlex project page.

      Derek

      • Boris said

        Hi Derek,
        thanks for the immediate answer.
        I opened a thread on the CodePlex page.
        When I want to load numbers (without decimal places) from MOSS into SQL Server 2005, it always says that the source is DT_R8 and the destination DT_I4 and that its incompatible. When I use a data conversion its okay but there are still the zeroes.
        I use a german system. The project still encounters problems with systems other than english as far as Ive read.
        Boris

  7. siva said

    Great post. I am doing the reverse way. I need to put the data from SQL to Sharepoint. I would like to identify the latest row in the sharepoint (basically latest_transaction_date). Do you know how to get that info from sharepoint list using the sharepoint adaptors.

    • Hi Siva

      Not sure if this is possible with the SSIS Components. I know CAML queries can be used to order items using element and there is also a element but don’t know if the SSIS components understand these. If they did you could construct a query list to return row with last transaction date.

      I guess you could use Access 2007 to connect to the SQL table and the SharePoint list and run a query there, alternatively if you are planning to look at SharePoint 2010 then Business Connectivity Services might help.

      Derek

  8. […] amount of hits and comments on previous blog posts I’ve written which touch on this subject, like this one on using Integration Services to get SharePoint list data into a […]

  9. I’ve written a review of Simego Data Synchronisation Studio here, might be of interest if you are looking at this post.

    Derek

  10. Tom, Tijeras, NM said

    I’m using from CodePlex a set of SQL Server Integration Services Community Samples, including SharePoint List Source and Destination Components: http://sqlsrvintegrationsrv.codeplex.com/releases/ view/17652 . Under SQL 2008 R2 when I import data I’m getting in many of the text columns, for teams for example: “;#Web Team;#”. Might you know if there is a component setting to remove this? I also am getting for assigned to “6;#Doe, John” where the number 6 appears to be an id for the value.

  11. K said

    Amazing, Worked like a charm.

    The Word Document linked on this page also really helped:
    http://msdn.microsoft.com/en-us/library/hh368261.aspx

Sorry, the comment form is closed at this time.

 
%d bloggers like this: