Posts Tagged web service
SSIS Consume Web Service with Complex Types (GAC Avoided solution)
Posted by uright in SQL Server on 2009/12/14
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:
Escape and Unescape functions for Javascript and ASP.NET
I’ve been working on a chatroom application that requires some AJAX calls to submit messages from user input. Usually, when you want to pass string parameters into the web service, you may do something like this:
This jQuery script calls an asp.net web service method called submitMessage with two parameters sessionIdStr and messageText. The source of parameter messageText is from a input textbox. Since the user may enter any characters (including special characters), and if they have a single quote (‘) in their input, the above statement will break. To resolve this problem, we can simply make use of the escape and unescape functions from javascript.
The escape javascript function simply encode a string to remove all the special characters in order to ensure that messageText parameter is transmitted successfully within the ajax call. For more information about this function, click here.
Now that our messageText paramter can passed successfully to the web service, we may need to do an unscape operation within the web service method to revert it back to the original input. Microsoft has provided us with a very handy library in namescape Microsoft.JScript.GlobalObject.
Now we have the messageText parameter revert back to the original message. We may do our handling code accordingly.
Note that both Javascript and Micorosft.JScript.GlobalObject has escape and unscape operations, you may use the combination in the reverse way (within web service response).
This is it for this short tutorial. Recently, I’ve been dealing with the .NET System.Datetime and Javascript Date conversion, when I have sometime, I’ll share my experience with you here!