Sunday, 15 November 2015

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:

 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 

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.
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

2 comments:

  1. Hello friends, in our next post we are going to demonstrate the same example with Script Task. Hope our post is informative. We need your continuous support to continue…
    Please make some comments whether you like it or not and provide your valuable feedback.
    Thank you.

    ReplyDelete