Access and consume a Web Service from Integration Services

Sometimes you need to consume a Web Service that is specific enough for you not be able to use the default Web Service Task available on SSIS 2005.

In this example I'm going to show how to consume a Web Service that returns a .NET object (a serialized DataSet, for example). For this, we'll have to use a Script Component that allows us to insert some custom code that is able to do what we need. The Script Component will be part of a Data Flow acting as a Source that provides rows to Transformations down the pipeline.

Acessing a Web Service from a Script Component is pretty much similar to how we'd do it in a standard .NET application:

1. You need a proxy class (in the VB language) for the Web Service you want to consume. You can create it using the wsdl.exe application like this:
   wsdl /language:VB http://yourserver/yourWebService?WSDL /out:yourProxyClass.vb
2. Access the Script Component's code and, on the code window, go to the Project menu and select "Add Existing Item". The item you have to add is the proxy class you created previously (yourProxyClass.vb, in this example). Next, go to the Project menu, select "Add Reference" and add the System.Xml and System.Web.Services assemblies.
3. Now you have to write the code to acess the Web Service and output its results onto the outputs you defined for the Script Component.
   In this example, the Web Service defines a class called Export which has a method called getDataSet(String) that outputs is a serialized DataSet. The Script Component has one output, called Output0, with two columns (colOne and colTwo) defined as a String (DT_STR)

Public Class ScriptMain
Inherits UserComponent


Dim webServiceExport As Export
Dim dsResults As DataSet

'Prepare the required connections and initialize objects
Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
MyBase.AcquireConnections(Transaction)

webServiceExport = New Export()
dsResults = Nothing

End Sub


'Get the data from the WebService
Public Overrides Sub PreExecute()
MyBase.PreExecute()

webServiceExport.Timeout = 3600
dsResults = webServiceExport.getDataSet("test parameter string")

End Sub


'Go through the received data and add it to the output rows
Public Overrides Sub CreateNewOutputRows()

Dim dtResults As New DataTable

dtResults = dsResults.Tables(0)

Output0Buffer.colOne = dtResults.Columns(0).ToString()
Output0Buffer.colTwo = dtResults.Columns(1).ToString()

Output0Buffer.AddRow()

End Sub

End Class

If you need some Web Services for testing you can search at http://www.webservicex.net/WS/default.aspx

Detailed information links:
Wsdl.exe: http://msdn2.microsoft.com/en-us/library/7h3ystb6(VS.80).aspx
Referencing Assemblies in a script: http://msdn2.microsoft.com/en-us/library/ms136007.aspx
Using the Script Component as a source: http://msdn2.microsoft.com/en-us/library/ms136060.aspx