Complete demonstration of Web Service Task in SSIS

Introduction

In this article we are going to demonstrate the retrieving data by web service task and store it into a Database table. For that we need

1.. Web Service task to retrieve information from Web Services
2.. XML task to Delete the multiple name space from XML document if Exists
3.. Data Flow task to read the XML file and store it into DB Table.

Here we are using www.webservicex.net to get the information of the web services. We are using Global Weather details for our country (major cities).

Web reference:

 http://www.webservicex.net/New/Home/ServiceDetail/56

 The Server URL that we used is http://www.webservicex.net/globalweather.asmx?WSDL

Before start it is recommended that you visit the URL for Global Weather and test it by GetcityByCountry by providing country name “india” and press the Invoke Button in the web page. It will generate a XML output.

Hope it will be informative and you enjoy the session.

How we do that

Step – 1 [ Retrieving the Data by using Web Service Task ]

First we create an HTTP connection manager. In the server URL we provide 

http://www.webservicex.net/globalweather.asmx?WSDL

As it is a free web service, so no need to provide use credential options.  Click on Test Connection.

Drag and Drop the Web Service task in the Control Flow task. The settings are mentioned bellow.

General Tab of Web Service Task

Property	Values
HTTP Connection	http://www.webservicex.net/globalweather.asmx?WSDL
WSDL File	E:\Preparation\SSIS\WSDL\myWSDL.wsdl
OverWrite WSDL File	TRUE

Then we click the Download File Button. It will automatically download the WSD File in the specified folder. In our case it is myWSDL.wsdl

Input Tab of Web Service Task

Property	Values
Service	GlobalWather
Method	GetCitiesByCountry
CountryName	India

You can get those service and method name form WSDL file. Open the WSDL file and read it carefully.

Output Tab of the Web Service task

Property	Values
Output Type	File Connection
File	WeatherReport.xml

The XML file is store in the location E:\Preparation\SSIS\WSDL in our case.

Step-2 [ Using XML Task to Remove Multiple Name Space if Exists ]

Now our WatherReport.XML file is ready to use. But sometimes we find a problem that multiple name space is in the XML file. We have to generate XSD later with this XML file. With multiple Name Space it is not possible to generate XSD file. So we have to remove all the name space from our XML file.

Here we are using XML task for that.

First we have to create a XSLT file for that. In our case it is MyXSLTFile.xslt

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

       <xsl:output method="xml" indent="no"/>

       <xsl:template match="/|comment()|processing-instruction()">

              <xsl:copy>

                     <xsl:apply-templates/>

              </xsl:copy>

       </xsl:template>

       <xsl:template match="*">

              <xsl:element name="{local-name()}">

                     <xsl:apply-templates select="@*|node()"/>

              </xsl:element>

       </xsl:template>

       <xsl:template match="@*">

              <xsl:attribute name="{local-name()}">

                     <xsl:value-of select="."/>

              </xsl:attribute>

       </xsl:template>

</xsl:stylesheet>

Property	Values
Operation Type	XSLT
Source Type	File connection
Source	WeatherReport.xml
SaveOperationResult	True
Destination Type	File Connection
Destination	WeatherReportFinal.xml
Overwrite Destination	True
Second Option Type	File connection
Second Option	MyXSLTFile.xslt

Step – 3 [ Use Data Flow Task to Save it into Destination Table ]

Here we are using online xsd generation and create the xsd file.

http://www.freeformatter.com/xsd-generator.html

In our case it is WatherReportFinal.xsd

XML Source Editor

Destination Table

CREATE TABLE tbl_CountryCity

  (

    Country       VARCHAR(50),

    City          VARCHAR(50)

  )

GO

Step – 4 [ Control Flow Task  and Data Flow task ]

Hope you like it.

Posted by: JOYDEEP DAS