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.
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.
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.
-
The first step is to create a new package named “web_service_task”
-
Afterward, drag and drop web service task component from SSIS toolbox (common) to Control Flow
-
Third steps are double-clicking on web service task in General menu, set up HTTP connection where you need to click on <New Connection>
-
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
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
-
click on Test Connection to verify your connection
-
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
IE: \WsdlFile\a1.wsdl
-
Now I click on OverwriteWSDLFile = Ture, which simply means I am going to override a1.wsdl file, in case if WSDL file already exists
-
What is next is hit Download WSDL button which means, download WSDL file from Http connection
-
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
-
In addition to the above, you can change the name and description of the web service task if you want
-
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
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.”
-
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”
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.)
-
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
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.
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
- SSIS Web Service Tasks - May 14, 2019
- Spatial SQL data types in SQL Server - July 11, 2018
- The HashBytes function in T-SQL - May 16, 2018