The XML Task in SSIS allows you to parse through an XML file and read the nodes in the XML. In a previous blog I showed how to use a web service task to get the city and state when entering a zip code.

The results we got back from the web service were in XML format and we saved them into a variable named Results. This image shows the value in the variable


We need to get the city and the state out of this XML and save them each into a variable. You will need to create an XML task in the Control Flow of a package. Set the operation type to XPATH. The source is going to be the results variable. The destination will be the city variable. The second operand will be the node you want to be read, “//CITY” in this case. Last we set the XPATH Operation to values because you want the value of the city node.


The XML Task that retrieves the State value will be identical except for the Second Operand and the Destination variable.


After all of this is complete the package with the web service task will come before the XML Tasks. I placed a break point on the last XML Task and I am showing the results of the variables in the watch window. You can see that the results contain the XML, the City, and State variables contain the values from their respective nodes.


This entry was posted in SSIS, Syndication and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s