How to Use SSIS Web Service Tasks for Data Retrieval

October 3, 2024

36

SQL Server Integration Services (SSIS) is a core component of the Microsoft SQL Server ecosystem that provides you with a versatile services toolkit for different types of data integration and transformation. Among many ways to pass data using the SSIS data integration tool, Web Service Tasks take up a special place, enabling you to fetch and load data, smoothly integrate applications with Cloud services, and handle real-time data exchange. Moreover, SSIS Web Service Tasks are a basis for data extraction and loading processes in ETL, which is widely used in data warehousing and facilitates business intelligence.

From this article, you’ll learn what SSIS Web Service Tasks are, explore the benefits of using this way to pass data, and get detailed instructions on how to create the SSIS Web Service Task.

What is a SSIS Web Service Task?

The Web Service Task in SSIS provides a straightforward mechanism for interacting with web services to retrieve remote data. It functions by connecting to a specified web service endpoint using the SOAP protocol, sending a request message, and receiving a response message.

It consists of a standard package task. To access it, simply go to the SSIS Toolbox panel. Under the Common category, you will find there the package named Web Service Task.

Simply put, the Web Service Task acts like a translator, bridging the gap between SSIS and web services. It takes the requests from SSIS and translates them into the language web services understand, and vice versa.

Key properties of a Web Service Task

When you open the Web Service Task Editor for the first time, you see a number of fields that refer to General properties used to execute a web method using an HTTP connection.

Let’s overview them.

HTTP Connection is the connection in the SSIS Package created to connect with the web service.
WSDL file defines the interface of the web service, specifying the operations, parameters, and data types involved.
Name and Description are the fields where you have to provide a name for the task object.

Service refers to the specific service exposed by the web service to be consumed by the task.
Method is the operation that will be executed by that service.

Output type: This parameter determines the destination for the web service’s response, either a file on some local or network path or a variable to be used inside the package.

Now that you have a general idea of the properties you can use, let’s briefly overview the advantages of the SSIS Web Service Tasks.

Benefits of using Web Service Tasks

Such a robust connectivity tool integrated into a data processing suite like SSIS is an immensely valuable feature. The absence of this functionality would require utilizing third-party tools, which isn’t optimal and would’ve required either extra resources or complex workarounds to set up integration. An integrated task for communicating with web services, on the other hand, enables quick, controlled integration with fewer errors.

Another significant benefit of using SSIS Web Service Tasks is that you can use them to consume web services at any stage of your data processing chain, ensuring timely and accurate data flow, e.g., when you intend to set up automation of data retrieval through scheduled SSIS packages.

How to create SSIS Web Service Tasks?

Let’s examine a simple yet comprehensive and functional guide for implementing processes that interact with web services.

Preparation

Let’s configure the Web Service Task. Initially, upon opening a new SSIS project in Visual Studio, you will have to drag the Web Service Task from the SSIS Toolbox, as illustrated below.

General parameters configuration

The configuration window will appear when you double-click the task.

Setting up the connection

Note that for test purposes, we will use an online calculator, which is a free access web service. But you can replace it with another web service URL that fits your project’s needs.

In the HttpConnection property, click the empty cell on the right and choose <New connection>. This opens a new window where you can configure the connection.
Enter the web service URL: http://www.dneonline.com/calculator.asmx?wsdl
Now, put the endpoint and pick Test Connection to ensure everything works. If everything runs smoothly, click OK to continue.

Configure the WSDL file

Now, let’s specify a local file to store the WSDL data and enable Overwrite WSDL File. Your General configuration screen should now look like in the image below.

To finish this step, click the Download WSDL button, determining the path to store metadata. Next, press OK.

Selecting and configuring the service, method, and its parameters

Now, let’s set the Input section configuration as in the screenshot below.

As you can see, you can select many parameters for the service and method.

Let’s briefly overview the parameters for this particular web service.

Service = Calculator. This web service endpoint could provide more than one specific service. In this case, we select that one.
Method = Add. This particular service, being a calculator, has four operations: Add, Subtract, Multiply, and Divide. You can select one of them.
Value = For. To simplify things, let’s set two fixed values to be added by the web service. For a real functional implementation, you could use variables instead.

Connecting outputs to Data Flow

Let’s take the output of the Web Service Task to use it later in our package. This way, it’s possible to create a variable to store the results of the method executed in this task.

To proceed, first check if the output screen configuration looks the same as the picture below.

Click OK to complete the task configuration.
To get a comprehensive overview of the data flow, let’s add an Execute SQL Task and connect it with the Web Service Task, as shown below.

The Execute SQL Task will be pointing to a table in an SQL Database. Here are the details in dbForge Studio for SQL Server:

If you want to create the structure, create an empty database called TEST_db and then run the following script:

USE [TEST_db]
GO

CREATE TABLE [dbo].[WebServiceOutput](
 	[datetime] NULL
) ON [PRIMARY]
GO

Next, return to our SSIS Package and configure General Tab in the Execute SQL Task, pointing to the database.

The SQL Server Statement will be included below. The query will insert two values in the table; the first is going to be the value from the web server output, and the second will be a simple TimeStamp to get information about the time when it was inserted.

INSERT INTO [TEST_db].DBO.WebServiceOutput
	(Value, TimeStamp)
VALUES        
	(?, GETDATE())

Now, let’s configure the Parameter Mapping tab. To do it, you have to add the previously created variable to get the data extracted with the Web Service task.

Running and testing the package

The web service is going to calculate a sum of two numbers we provide fixed (500, 500). The expected output will be the response from the service with the 1000 value.

To run and debug the package, let’s click the Start button.

Next to that, we are going to see the correct execution of all the steps of our data pipeline almost instantly.

If you have followed these instructions, your pipeline is also going to work seamlessly.

Now, it’s time to check the results in the database. Here, you will see the data stored in the table with its corresponding TimeStamp.

Here are the results after several executions, with different timestamps in the table. The last one, which has different parameters, proves that the calculated value from the web service works as intended.

Useful tips for running SSIS Web Service Tasks

When you work with SSIS packages, there’s a general recommendation to construct them in the most modular and atomic way possible. Make sure you clearly separate each step according to functions or any chosen criteria. It is a good practice to build packages on top of previously tested blocks. This approach will save you a significant amount of time and help you quickly find specific errors the moment they sneak in.

For the specific case of web service tasks, it is recommended to work with the services using a testing tool that you are already familiar with. Errors in web service task parameters are easy to make and can lead to lengthy debugging sessions to find simple mistakes that are tough to pinpoint within SSIS’s complexity.

Using SSIS Web Service Tasks with Devart SSIS Components

Devart SSIS Components significantly enhance the power of SSIS by providing a wide range of connectors and integration capabilities. With the SSIS Components from Devart, you can easily connect to web services and combine data from various sources, making your data integration projects more efficient and flexible.

Here’s a quick example to illustrate the power you can achieve with this synergy of tools. Let’s imagine that you want to use information from the online currency exchange rate web service to impact your own data from a database, and then you will have to finally output that data to your own or an external Magento-based website. This can be easily done through SSIS data flow using Devart SSIS Components.

Of course, this represents just one of many potential application scenarios. SSIS Components streamline connectivity, supporting numerous data sources, from popular databases to cloud platforms and data warehouses. Thus, you can use this tool in dozens of unique cases.

Check the Devart SSIS Components to empower your development processes!

Conclusion

Integrating web services with SSIS greatly amplifies its power. By connecting to external APIs and services, SSIS packages can access and process data from various sources.

Devart SSIS Components, on the other hand, provide additional features and connectors to enhance the capabilities of SSIS packages.

Ultimately, it is up to you to design the most efficient and powerful solutions that meet your project’s needs. However, there is no doubt that SSIS can provide significant benefits to any organization.

Feel free to explore the Devart SSIS Components to boost your development workflow!

How to Use SSIS Web Service Tasks for Data Retrieval

Table of contents

What is a SSIS Web Service Task?

Key properties of a Web Service Task

Benefits of using Web Service Tasks