Using SSIS To Get Data From a Web Service

• Download source - 22.2 KB
• The sample package outlined can be downloaded here.

SSIS has a web service control flow task that allows you to call a web service and store the results. This can be very useful if you need to call data from a web service that will be stored in an OLTP or OLAP database. The configuration of the web service task is pretty straight forward as all you need is:

1. The address of the WSDL
2. The service that will be called, which is provided from the WSDL
3. The method that will be called, which is also provided from the WSDL
4. A file location for the results to be stored

So let me lay out some example requirements and step through how SSIS can be used to fulfill them. First, we need to get the weather based on the zip code from the web service found at:

• http://wsf.cdyne.com/WeatherWS/Weather.asmx?WSDL

The service, Weather, will be used and the method, GetCityForecastByZip, will be called to return the weather based on a zip code passed to the method. The XML results from the web service need to be saved and then an XPATH query needs to be passed to return the weather forecast.

To get started, we will use a web service control flow task and configure it to use the defined WSDL. From the web service configuration pane, create a new HTTP connection:

From within the connection manager pane, enter the WSDL address:

Click OK to go back to the web service configuration panel and define where the WSDL file should be stored in the file system. There are several ways to generate the WSDL file:

1. Copy and paste the web service address into a web browser and then copy and paste the XML into Notepad and save it in the location you wish to point at.
2. Create an empty file with the name you choose and the extension of “wsdl”. Point the WSDL towards that file and once done, click the Download WSDL button.

Once the WSDL location has been set, the Download WSDL button is enabled and clicking this will call the web service and save the WSDL in the file location specified. In order to download the WSDL, you MUST ensure that the OverwriteWSDLFile is set to True otherwise an error will be thrown.

With the connection and WSDL defined, we can now define the service and method to be called from the Input pane. Both the Service and Method are read from the WSDL file defined in the previous step and both are drop downs. If there are no available services or methods, then there is an error in the WSDL file and it should be recreated or re-downloaded. In this example, we will be using the Weather service and GetCityForecastByZip method.

Once the method is selected, the parameters accepted by the method are exposed in the pane underneath the service/method configuration pane. In this case, there is a single parameter, zip, that is accepted. The value passed can either be statically entered or provided within an SSIS variable. For this example, go ahead and use “33914” so we can see what the forecast is for sunny Cape Coral Florida.

The following steps have completed:

1. The web service address, WSDL
2. The service to call
3. The method to call
4. Value(s) of any accepted parameters

We now need to configure where the XML results will be stored by configuring the Output. The output can be stored in either a variable or file. Storing in a file requires that a file connection manager be created, this is a very straight forward task and for brevities sake, I will use an existing file connection manager, C:\SSIS\Weather.xml.

The web service task configuration is completed and we can validate its configuration by executing the package. If all complete successfully, then the results should look similar to this:

With our results populated, the next step is to use an XML task to pass an XPATH query to return the forecast. Reviewing the structure of the XML results, the path for the forecast would be passed like this “/ForecastReturn/ForecastResult/Forecast[1]/Desciption”. Before configuring the XML task, first create a variable called forecast that is a data type of string that will be used to hold the forecast from the XML task:

The XML task needs to be configured to:

1. OperationType of XPATH
2. SourceTypefile connection
3. Source weather.xml
4. DestinationType of Variable
5. Destination User::forecast
6. OverwriteDestination of true
7. SecondOperandType Direct Input
8. SecondOperand /ForecastReturn/ForecastResult/Forecast[1]/Desciption
9. XPATHOperation of Values

This should provide the steps necessary to capture the first forecast description, PLEASE note that the element in the XML is misspelled, Desciption, and assign it to our variable. To display this value, we can use a VB.NET script task that will set the forecast variable to ReadOnly and then in the code, use a MessageBox Show method to display the variable:

.Show(Dts.Variables(0).Value.ToString)

The package should resemble this:

By executing the package, all tasks should complete successfully and finally an empty message box should pop up.

What?!?! What went wrong?!? The issue that arises is the namespaces that are within the XML results. The XPATH query doesn’t fail, it just doesn’t return any results because of these namespaces. Depending on the formatting of the results, this may not be a problem, but I intentionally picked these results to demonstrate the ability to remove the namespaces within the results. After some time searching, I found an AWESOME post on using C# to remove namespaces here. Thank you Kirk Evans!!

So I will add another script task that will:

1. Read my weather.xml file
2. Remove the namespaces
3. Rewrite the file without the namespaces to C:\SSIS\weathernew.xml

With only minor changes made to the script from Kirk, the code should look like this:

public void Main()
{
XmlTextReader reader = new System.Xml.XmlTextReader("C:\\SSIS\\weather.xml");

XmlTextWriter writer = new
System.Xml.XmlTextWriter("C:\\SSIS\\weathernew.xml", System.Text.Encoding.UTF8);

writer.WriteStartDocument();

while (reader.Read())
{
switch (reader.NodeType)
{
case XmlNodeType.Element:
writer.WriteStartElement(reader.Name);

if (reader.HasAttributes)
{
//Cannot just use writer.WriteAttributes,
// else it will also emit xmlns attribute
while (reader.MoveToNextAttribute())
{
if (reader.Name.CompareTo("xmlns") != 0)
writer.WriteAttributeString(reader.Name, reader.Value);
}
reader.MoveToElement();
}
if (reader.IsEmptyElement)
{
writer.WriteEndElement();
}
break;
case XmlNodeType.Text:
writer.WriteString(reader.Value);
break;
case XmlNodeType.CDATA:
writer.WriteCData(reader.Value);
break;
case XmlNodeType.ProcessingInstruction:
writer.WriteProcessingInstruction(reader.Name, reader.Value);
break;
case XmlNodeType.Comment:
writer.WriteComment(reader.Value);
break;
case XmlNodeType.EntityReference:
writer.WriteEntityRef(reader.Name);
break;
case XmlNodeType.EndElement:
writer.WriteEndElement();
break;
}
}
writer.WriteEndDocument();
writer.Flush();
writer.Close();
reader.Close();

Dts.TaskResult = (int)ScriptResults.Success;
}

Now by pointing the XML task to the weathernew.xml file and re-running the package, a message box should appear with the forecast for sunny Cape Coral Fl.!!

Whatever. At least my package worked!