I am going to explain a simple way to load a SharePoint list with the information stored in a SQL table or other source accessible from SSIS. The objective is to create a mechanism that updates the content of the SharePoint list periodically with the data stored in a table. The solution is obvious but I will expose the different approximations to the solution and the alternative that I implemented. BDC: My first thought was use the BDC to expose the information through the SharePoint site, but it was too complex (I only wanted to show the info in a table) and this solution was not compatible with WSS. Object Model: We can create an assembly that updates the content with the SharePoint API. This solution would be acceptable if the SQL Server and the SharePoint server was in the same machine, but this scenario is too simple for a real situation. In addition, it involves deploying the assembly through all the servers in the farm (using a feature) and creating a SPJob to execute it periodically. SSIS Web Service Task: This would be the ideal solution, but this SSIS task is unable to invoke the SharePoint web services L. Custom SSIS task: Whether the out-of-the-box task in SSIS is unable to invoke a web service, why we cannot create a custom SSIS task? Well, I prefer to wait until a future version of SSIS that include it J. SSIS Script task: This is my solution, but I am sure that it is not the best solution to all cases. In my case the information was about 15.000 rows and the load of all content was completed in 30 minutes. In others cases it may be unacceptable and the only acceptable solution would be the BDC. Related Links:
This is the solution that I adopted. It consists in the extraction of the information with a common SSIS package and, at the moment of the load of the content to the SP list, a SSIS script task inserts the data in the SharePoint list through the WSS web services. The main problem is how to invoke the web service and for that reason I used the wsdl.exe to create a proxy class which was able to invoke the web service. Then I added this proxy class to the script task and gave the appropriate permission to the list in order to the SQL Agent identity was able to invoke the web service. So, the solution only consists in a SSIS package that an SQL Server job executes periodically.
http://msdn2.microsoft.com/en-us/library/7h3ystb6(vs.80).aspx
http://msdn2.microsoft.com/en-us/library/lists.aspx
Sunday, September 30, 2007
SharePoint and Integration Services
Subscribe to:
Post Comments (Atom)
Posts
4 comments:
You can now connect to Sharepoint easier - a public ssis adapter for sharepoint (a source and destination adapter) has been released:
http://www.codeplex.com/SQLSrvIntegrationSrv
It handles loading data (optimizing it if you remove columns from the flow), updating data and allows deleting from Sharepoint all within a dataflow.
I could not find this information on the URL you provided. Is this only for 2008?
I am trying to use the SSIS Web Service Task to leverage the SharePoint Lists Web Service, to pull items out of SharePoint and into my SQL Server 2005 database. I can set up the HttpConnection (http://sp_servername) and test this fine, but when I add the WSDL file (http://sp_servername/_vti_vbin/Lists.asmx?WSDL), I get an error stating "The filename, directory name, or volume label syntax is incorrect. (Exception from HRESULT: 0x8007007B)."
The sample is provided for Sql 2008, but recompiling it to use the Sql 2005 objects should be a quick replacement of some namespaces at the top.
In the releases section, at the bottom is the Sharepoint Source and Destination adapter.
Kevin
To give an update to this post, there has been a 2005 enabled SharePoint tool at: http://www.codeplex.com/SQLSrvIntegrationSrv/Release/ProjectReleases.aspx?ReleaseId=17652
SharePointListAdaptersSetupForSqlServer2005.msi is the adapter for 2005
Jeremy
Post a Comment