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

wcf

wcf

Step 2:

You can test the service by pressing F5.

service

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.

new

Step 4:

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

Web Service Task

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.

connection

Step 6:

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

Test

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.

WSDL

WSDL

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

New

Step 9:

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

Create file

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.

editor

Step 11:

Run the SSIS project.

run

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