Nikhilesh Patel
How Web service task communicate with applications?

SSIS Web Service Tasks

May 14, 2019 by

SQL server Integration services provides us with SSIS Web Service Tasks which assist us to drive web services.

This article will walk through how to configure the SSIS Web service task in Visual Studio 2017 (SSDT). In commence, it starts with the definition of Web service, and then dip drive of the configuration of web service task in SSIS including a salient example.

In addition, I’ll discuss

  • How to create a new Http connection in web service task with or without proxy authentication
  • Then I will describe how to download WSDL (Web Service Definition Language) file in web service task
  • Furthermore, I will give a demo of How to configure web service and drill down its the method which does not have a parameter
  • Finally, I will also explain how to get the output of the Web Service in either form of File

What is Web service?

Web services are a kind of system where they can exchange XML-based information that can be used to throw over the Internet for interaction with one application to another application.

To be more specific, there are many operational applications in the worlds which work with create data, manipulate and sometimes generate a report. Each of these applications may be written with .NET, JAVA or any other languages. If you want to communicate with this application, you must write code in those languages. To solve this problem web service is introduced.

Webservice is well-known as a global way to communicate with the applications. You can send data to that application and receive data from that application. One of the rudimentary aspects of this is, web services are platform independent, and language independent for an instance java web service can be used in .net application or vice versa.

The key for doing this is, web service is an XML base. As we already know that XML is the global way to keep data and standardize that data, web service function same, generate XML for returning data. On top of that, it can get variable and pass parameter as XML as well.

How Web service task communicate with applications?

Web service in SSIS

In SQL Server 2005, the Web Service Task has been introduced in SQL Server Integration Services. With the help of this, we can able connect to a web service and it allows us to execute any one of the available web methods which can be exposed by the Web Service.

To be more specific, the web service task helps us to read data from the web service and also sent data to web service. Web service task work with the web service definition file (.WSDL). And expose its service, method, and list of the parameter of each method if they have.

If I talk about parameter then, either parameter can be set from package variable or directly with static values. In that case, if you want to work dynamically then work with the variable. However, you can manage to work directly with a static value.

Although, output result will be written in XML form only in file or variable.

SSIS web service task

Here I am going to give step by step guide to work with the web service task of SSIS

Before starting it, let find web service, which is available free on the internet. Below is the URL where we can find the football world cup information online. https://footballpool.dataaccess.eu/info.wso?WSDL

Now I’ll provide you a step by step approach to work with the web service task.

  1. The first step is to create a new package named “web_service_task”

    Create new SSIS package

  2. Afterward, drag and drop web service task component from SSIS toolbox (common) to Control Flow

    SSIS web service task

  3. Third steps are double-clicking on web service task in General menu, set up HTTP connection where you need to click on <New Connection>

    Set up SSIS web service task

  4. When clicking on <New Connection> it automatically open HTTP Connection Manager Editor wherein server setting needs to specify a Server URL: https://footballpool.dataaccess.eu/info.wso?WSDL

    WSDL file generate

    Furthermore, you may specify time-out and chunk size appropriately or leave it default

    Although, if u need to do proxy setting then specify proxy URL and authentication to connect to internet from your working environment LAN network, you can use credential or go to proxy and setting required things

  5. click on Test Connection to verify your connection

    Test WSDL file.

  6. Now the next step required to choose WSDL file, for that first we need to create a blank file with WSDL extension. Here for demo purpose, I am creating one empty file a1.wsdl which is 0 kb means blank. I am storing it in WsdlFile folder for proper arrangement

    Blank WSDL file

    IE: \WsdlFile\a1.wsdl

  7. Now I click on OverwriteWSDLFile = Ture, which simply means I am going to override a1.wsdl file, in case if WSDL file already exists

  8. What is next is hit Download WSDL button which means, download WSDL file from Http connection

  9. Now check the location where you have created that a1.WSDL files, you may find the WSDL contain some XML code and you may check the size of the file it becomes 0 to 64 kb this clearly means that there are some codes in the file

    Download WSDL File

    Successful message of generate WSDL file

    Look like of WSDL file

  10. In addition to the above, you can change the name and description of the web service task if you want

  11. Now move on to the input task where on service, select “Info” which indicate the service name of particular WSDL. Afterward, the select method depends on your requirement. You may get method information when you select any method. Some method either requires input values or directly provides a result

    Input Method

    Here I am going to do a demo without using the input method, so we select “AllPlayersWithCards” method what is AllPlayersWithCards?

    • Note: You may find a detailed description of a particular method in “WebMethodDocumentation”.

    Hear this method “Returns an array with the names of all players that have a yellow or a red card. The number of yellow and red cards is returned.”

  12. Next step is going to Output tab where you have to specify output type: file connection/variable here I am choosing the file for demo purpose

    This file contains the result of that method we have mentioned above

    For that first click on <New Connection> then in file connection manager select usages type and file name. Here I am selecting “Create File” in usages type and browse the location of the file where WSDL file is store and gives name as output.xml. As we know that result of WSDL file stored in XML, create XML file out.xml for storing result. You may also first create “.xml” file and choose the existing file in file connection to manage “usages type”

    Create blank file for output

    Path

    This is all that is needed to configure to set up web service task. Now we need to execute the package This package execution take few seconds as it takes data from the internet (it depend on the speed of internet and respond of the particular website.)

    SSIS web service task

  13. Now check the result file. We can see output.xml file contain names of all players that have a yellow or a red card in XML form. To be more specific, you can XML task in the data flow to get that XML data into the database or manipulate it

    Result in XML of SSIS web service task

Conclusion

A SSIS web service task is used to capture info from Web service over the internet and returning result into the XML file. Moreover, in my next articles, I will elaborate on the parameterized method of the Web Service task and manipulate return XML data into a database table.

Nikhilesh Patel

Nikhilesh Patel

Nikhilesh Patel is a database professional having 7+ years of experience. Most of his vocation focuses on database design, development, administration, performance tuning and optimization (both SQL Server and Oracle). He has collaborated with SQL Server 2000/2005/2008/2012/2014/2016, Oracle and PostgreSQL databases. He has fabricated and developed databases for insurance, telecom and communication domain. He is a database administrator at HighQ solution. He continuously develops his professional skills to keep accelerating with new technologies.

In his downtime, he enjoys spending time with his family, especially with his wife. On the top of that, he loves to do wondering and exploring different places.

Say hi and catch him on LinkedIn
Nikhilesh Patel

Latest posts by Nikhilesh Patel (see all)

Integration Services (SSIS)

About Nikhilesh Patel

Nikhilesh Patel is a database professional having 7+ years of experience. Most of his vocation focuses on database design, development, administration, performance tuning and optimization (both SQL Server and Oracle). He has collaborated with SQL Server 2000/2005/2008/2012/2014/2016, Oracle and PostgreSQL databases. He has fabricated and developed databases for insurance, telecom and communication domain. He is a database administrator at HighQ solution. He continuously develops his professional skills to keep accelerating with new technologies. In his downtime, he enjoys spending time with his family, especially with his wife. On the top of that, he loves to do wondering and exploring different places. Say hi and catch him on LinkedIn

1,457 Views