Using Web Services with SSIS Vb.Net

Using Web Service in SSIS with VB.NET script

SSIS provides two methods of handling Web services.

 

One option is the GUI Web service task that allows you to enter the Url of the Web service in a text box and select from a drop down box the list of methods it contains.

 

This approach is useful if it's a straight forward call to a Web service that requires minimal intervention on part of the SSIS package.

　

The second alternative is to use VB.Net to call the Web Service.

This is useful if there is more of an requirement to manipulate the source or destination data.

When working with VB.netin SSIS it might become apparent, how does SSIS know the methods of the Web Service.

 

You cannot start using the methods in the VB.Net script as its doesn't know what they are.

This is when a Proxy Class is used. This contains the definitions\properties of the Web Service.

To setup the Proxy Class one technique is to use the

Wsdl.exe tool.

 

Usually located under directory C:\Program Files\Microsoft.NET\SDK\....\Bin.

It's useful to generate the Web Service Contract file locally onto your machine.

 

You would run the following command in the command prompt to generate the Wsdl contract file onto your targer machine.

 

wsdl /language:VB /out:myProxyClass.vb http://hostServerUrl/WebserviceUrl/WebServiceName.asmx?WSDL

 

Then in the SSIS VB.Net IDE, you would add the Wsdl contract file by selecting the "Add Existing Item" and choose your newly generated wsdl file.

 

You will now have the Proxy Class so you can begin to use the methods of the Wsdl in your VB.Net script.

You will still need to code the Url of the Web Service within the script to interact directly with the Web Service host.

Some suggestions are the Url can come in the form of a SSIS variable, Config file entry or whichever is best suited for your application.