How to Consume Web Service In SSIS

Introduction

For this Tech Tip we will create a simple WCF service and consume the same in a sample SSIS package.

A Web service can simply be defined as a piece of functionality hosted over the web. It is a platform-independent technology that can be leveraged using xml messages.

Let’s go through the steps one by one:

Step 1:

Create a WCF service using the “WCF Service Application” template in visual studio and add a method GetCustomerName to the WCF service as shown below..





Step 2:

You can test the service by pressing F5.



Step 3:

So, our WCF service is up and running. Now let’s create the SSIS package to consume the service. Open SSDT or BIDS and create a new SSIS Project.



Step 4:

Drag and drop the Web Service Task onto the Design pane.



Step 5:

Double click on the Web Service Task to open the “Web Service Task Editor”. In the General tab set the HTTPConnection option by creating a new connection.



Step 6:

In the Server URL textbox, specify the WCF service address that we created earlier and press the “Test Connection” button.



Step 7:

Create an empty wsdl file in notepad and specify the path to this file in the WSDLFile property. Make sure that the OverwriteWSDLFile option is set to true and press the “Download WSDL” button.





Step 8:

For the purpose of this exercise, I have taken the web service output in a text file however we can use the response received from the WCF service in a variety of ways for example:

• Saving in the database.
• Creating/Passing a JSON object.
• Sending it to another application.

For now, just go to the Output tab, in the File properties, select "New Connection".



Step 9:

Select the "Create file" option in the Usage type.



Step 10:

In the File textbox, specify the path of the file to store the results of the Web Service and press OK. This is the file where the XML data will be written to when the package is run and the web service is called.



Step 11:

Run the SSIS project.



Step 12:

Open the file created in the path specified in step 10 and you can see the XML output that was created from the web service.

Read more articles on SQL Server: 

• New Features In SQL Server 2016
• Full Text Index In SQL Server