I came across a need to do a look-up on a zip code to see if was valid and what state it is in. The method that was suggested for doing the look-up was a web service. Pass in a zip code and it will respond with an XML document containing results.
A script transformation task in Integration Services works well here. After pulling the task into the data flow, the output columns from the look-up need to be added to the data flow. This is done by going to the “Inputs and Outputs” tab on the script transformation task and adding a couple of columns to the output. I suppose that you could go so far as to add a whole other output that reserved for rows that had a successful look-up and those that didn’t have a successful look-up.
The next item on the agenda is the interface with the web service. This is done quite simply on the command line. Visual Studio comes with a tool called wsdl.exe that takes a wsdl document and converts it into a web service proxy object. The syntax to use this tool goes like this:
wsdl.exe /l:CS /n:WService /out:bin/[NameOfOutputFile].cs http://[NameOfServer]/[NameOfService].asmx?WSDL
By using the /l switch we can output either VB or C# code.
After we have generated the source code for the proxy class, we need to get this code into our SSIS package. You can either copy past the source into the ScriptMain section of the script task, but I suggest that you add a new class to the script task and paste it in there. After the source is included in the task you should be able to write code like this:
Dim svc As New [NameOfClass]()
Dim response As XmlNode = svc.GetInfoByZIP(Row.zipcode)
For the example that I wrote, the GetInforByZIP method (I’m calling the synchronous method) returned an XmlNode that I assigned to a new XmlNode class.
After inspecting the returned XmlNode for children (if the look-up was invalid, it returned a document without child nodes), I assigned the output columns that is created in this task to denote whether the look-up was successful or not and the state that the zip code is assigned to.
While the performance wasn’t great – a couple hundred look-ups in 10 to 15 seconds – it still demonstrated that a look-up could be performed as a web service. This web service that I was using was out on the Internet somewhere and I’d bet that if it were a local service, the performance would be rather good.
Anyone else used the script transformation task to do something cool?