Using a Script Transformation Task for a Web Service Lookup

May 23, 2007

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?

Chris Webb’s Chalk Talk

May 9, 2007

I had the pleasure of attending a chalk talk hosted by Chris Webb at the MS BI Conference here in Seattle.  He spoke for a while about using MDX for KPI creation in Analysis Services 2005.  I hadn’t really thought about it, but I had previously used Business Scorecard Accelerator with Analysis Services 2000 and had some difficulty with calculating trends and status and he had great ideas for how to accomplish this.

I have a feeling we will be seeing a blog post soon.

Katmai release

May 9, 2007

I’m at the Microsoft BI Conference in Seattle and Jeff Raikes announced the next version of SQL Server, codenamed “Katmai” will be out sometime next year.

This seems interesting to me since a lot of the customers that I know of are still using SQL 2000 and are working right now to migrate to 2005.  It will be interesting to see if customers are able to get migrated and then be willing to start over again by upgrading to a new version of SQL.

I suppose some will skip 2005 and go to Katmai anyways.

Jeff announced that among other things that Katmai will have support for storing unstructured data in the form of documents.  It will be interesting to see how this takes shape.