Sunday, May 26, 2013

Simple steps to Consume a web service in SSIS package

This article is written for WCF service which is consumed in SQL Server 2008.

Sometime we required to insert master data provided by the service to the database. So we have two options

1. We can create another service for our domain to communicate and bring  data from source service and can insert into the database.
2. We can directly consume the web service in our database

Here we shall discuss about the second approach. SQL Server enable us to consume the web service directly. It offers "Web Service Task" component to consume the web service and can manipulate and write the response to different media like Database , file ,variable for further manipulation and processing.

Before start it we must have the "WSDL" file of the webserivce. WSDL file.

To get the wsdl file follow the following steps

1. Open the web service in your browser
2. after the url add ?WSLD. e.g  http://localhost/sample.svc?wsdl
3. Go to browser's file menu and choose "Save As..."
4. Save the file as "<filename>.wsld"


The following diagram depicts  the communication between the web service and SSIS package and the various data destinations like database , files etc







Its very easy to integrate/consume the web service in your Sql Server database and need to follow these simple steps

Step 1: Go to Start > All Programes >SQL Server 2008 >SQL Server Business Intelligence Development Studio

From this GUI select new project and you will see the following window



Step 2:  you will see the work area. And from the toolbar you can double click the "Web Service Task" to ad in control blow screen




Step 3: Once the task is created go to connection manager and right click in working area of connection manger to create the connection with web service. Select New Connection


Step 4: In new connection window you will see the many options. Choose the HTTP as shown below



Step 5 : Once you add the connection you need to provide the service URL. In case the service is running on HTTPs protocol then select the use certificate option also.

Since in our example service is running on simple http protocol so no need to select this option.


Step 6: To validate the connection simply click on "Test Connection" and the following screen will display.

Step 7 : In "General" screen. Provide the WSDL of the web service and  click on download wsdl. This step will import the WSDL and will get the information of contract and methods.


Step 8 : once the WSDL is downloaded you can select the service from the drop down and can select the methods implemented in the service as shown below



Step 9 : In output window select the destination of the extracted information from the web service.

Step 10 : Provide the output if the method is expecting any.As  in this case the method "GetCountryList" expecting one variable "aggregatorid" so we need to pass it here or we can make it configurable and keep somewhere in drive or database


Step 11: Everything is ready now. You can see the connection and output configuration in connection manager window and service task in control flow



Step 12: Now just click on Run button


Once it run go to the configured location and check the file.


So these were the simple steps to consume the web service through SSIS package. Once you get success in consuming the service you can extend and make it configurable further as per your requirements.

Happy Programming !!!
Post a Comment