A recent project I'm working on requires some data to be stored in SQL (for reporting purposes) but be manageable through SharePoint without creating custom edit/display forms since the data was used on several custom screens that we were already developing.  The logical step for this at the time seemed to be to use SharePoint Lists, then import/export them into a SQL database using SSIS... there are other ways to do this besides SSIS, but this seemed to be the quickest and easiest rather than creating custom workflows, event handlers, or edit screens.

While this sounds easy since SSIS can consume web services, it turned out to be a little more complicated since SSIS cannot consume certain SharePoint web services, specifically the Lists service.

Long story short, I was able to get a demo going that actually met all the requirements... I've posted some links below to explain how to do the whole thing but also provided a general outline on what it takes to get it done.

  1. Create a proxy class for your web service - this is actually very easy..
    1. create a new class library
    2. add the web reference to your web service
    3. sign the assembly
    4. build and copy the dll to the .NET framework 2.0 folder
    5. install in the GAC.
  2. Create a new SSIS package
    1. Add a script component to the control flow
    2. This script needs to reference your proxy class
    3. write the code to read from the lists which returns xml objects
    4. apply some XSLT to clean up the xml
      1. specifically need to fix the namespaces, SSIS apparently cannot read XML with multiple namespaces
    5. Add a dataflow task to save to the DB
  3. Add any additional logic/controls to the SSIS package
  4. Run it
  5. If successful, check your destination table/db

While it sounds like a lot of steps, you only have to do the first step (proxy class) once for each web service... then you can reference it in each SSIS package and specify the list to query.  The annoying thing is using the script component to query the lists and get it into a variable, but it didn't take much to do that and you can copy/paste for each use... the only change you'll have to make to the script is the variable name to store the results in and the list name to query.

References: