SSIS: Calling SharePoint web services from the data flow
In my previous post, I outlined how to call a SharePoint web service to obtain a list of all Excel files held in a document library. However, that code was developed in Visual Studio where adding a reference to a web service is simplicity itself. Doing the same in SSIS is more of a challenge as Visual Studio for Applications (VSA) does not have the Add Web Reference command familiar to Visual Studio developers.
To keep my script component simple, I decided to place it in the data flow so that I could pass a list of SharePoint sites on the input buffer and the component could output a list of Excel files on the output buffer. If you are familiar with the Script Component you will know that it can be used in three forms: as a Source, a Destination and as a Transformation. In its default form, the Transformation is synchronous (i.e. one line of output is written for every line of input). As I wanted the script component to generate more rows on its output than it received, the first thing I needed to do was to switch it to asynchronous mode by changing the SynchronousInputID of the output buffer to zero.
To call the SharePoint web service from within my SSIS script component I needed to create a Visual Basic proxy class using wsdl.exe using the following command-line:
wsdl /language:VB http://yourhost/_vti_bin/lists.asmx?wsdl /out:SharePointList.vb
This proxy class was then imported into the Script Component by selecting "Add Existing Item..." from the Project menu. I needed to delete the first few "garbage" characters and add references to the System.Xml and System.Web.Services .NET assemblies before the proxy class would compile.
Next I converted the C# code I developed in my console application (see previous post) to VB.NET using the excellent conversion routine from DeveloperFusion and pasted it into my script component. After modifying the code to work with the input and output buffers the whole package worked fine. Below is my final data flow.
Beware! If you need to call two different SharePoint web services from within the same script component, you will need to edit the code generated by wsdl.exe and add a Namespace in order to avoid conflicts.
