This blog explains the easiest method SSIS can consume web service methods consist of complex types as the request or response parameters.
If a web service method consist of complex types as the request or response parameters, we can’t use the build-in Web Service Task. Although, there are posts online where we can consume it programmatically within VSA (Script Task).
Since we can’t directly add Web Reference from the VSA editor, some people suggested to build a separate library project that make the web service calls, and let the VGA from SSIS reference this library. The problem with this solution is that the built library would have to be registered to GAC on server running the SSIS package. This may sometimes be policatically difficult epecially when the package is being executed by SQL Server Agent. We wouldn’t want to install library to GAC on a SQL Server machine for just making a web service call.
In order to avoid going down the path to touch GAC, what we can do is to use the WSDL.exe tool to generate the proxy class and import this class into the Script Task code, and consume the web service from there.
Proxy Class Generation
- Please open Visual Studio Command Prompt. It should located under “Visual Studio Tools” subfolder in the Start Menu.
- cd into an empty directory and type in the following command
- wsdl http://Domain/WebServiceURL?wsdl /l:vb
Import Proxy Class to VSA editor
- Add a new Script Task, and go into the Design Script window
- Add a new class under the Script Task project
- Copy and paste all the content from the generated class to this new added class file
- This is optional, you may wrap this class into a namespace to make it cleaner.
Consuming the service
We may now consume the web service by instantiating the client object. In order for the script knows which endpoint the client is calling, you may need to set the end point manually like this:
