Sync a SharePoint list to database table

Since SharePoint list provides the interface to edit/view data, you might want to use a list as the UI. But the data will be stored in the content database. Sometimes you might want the data to be used by other non SP applications for which the data might need to be in its own SQL Table. SharePoint exposes all its data using Web Services. One might ask then why do you need to sync a list to database table, why not call the web service directly from the other non-SP applications. The answer to that question is you can but for some reporting applications you might want your data to be in SQL, a weak argument but its a possibility. So how do you sync a list to SQL table using SSIS: You might be tempted to use the SSIS webservice task to call the SP List webservices, not so fast, the Web Service task supports primitive types only. If you look at the WSDL for method GetListItems, we will have a problem. So the solution is to use WSDL to generate the proxy, and use the Script task to call the webservice, you can add the proxy in the script task as reference. The next problem is the xml data that you get from the webservice method will have multiple namespaces in it, which will be a problem for the xmlSource to read the xml and pass it to the ado.net data source. So you need an XML task to remove multiple names spaces from the output xml. The xsl to remove the namespaces is:

<?xml version="1.0" encoding="utf-8" ?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="xml" indent="no" />

<xsl:template match="/|comment()|processing-instruction()">

<xsl:copy>

<xsl:apply-templates />

</xsl:copy>

</xsl:template>

<xsl:template match="*">

<xsl:element name="{local-name()}">

<xsl:apply-templates select="@*|node()" />

</xsl:element>

</xsl:template>

<xsl:template match="@*">

<xsl:attribute name="{local-name()}">

<xsl:value-of select="." />

</xsl:attribute>

</xsl:template>

</xsl:stylesheet>

In the script task dump the output in a variable and direct the xml task to get the data from the variable and overwrite it after applying the xsl. Do you see how powerful the varibles are in SSIS, you can communicate between tasks using the variables. In the XMLSource set it so it gets the data from the variable. To generate the columns in the xmlsource keep some sample data in the variable and let the xmlsource generate the schema so that you can map the columns to the ado.net data source. If you dont do the sample data in the variable, it would be hard to map the columns.

References:
http://www.sidatkinson.com/post/Extracting-SharePoint-Data-using-SSIS.aspx
http://www.devx.com/dotnet/Article/35070/0/page/1
http://blogs.pointbridge.com/Blogs/matyas_ethan/Pages/Post.aspx?_ID=2
http://sqlblogcasts.com/blogs/drjohn/archive/2007/11/02/Getting-a-list-of-files-from-a-moss-document-library-using-a-SharePoint-web-service.aspx
http://sqlblogcasts.com/blogs/drjohn/archive/2007/11/03/ssis-calling-sharepoint-web-services-from-the-data-flow.aspx