Click here to Skip to main content
15,867,308 members
Articles / Database Development / SQL Server

Calling a Web Service from a SQL Integration Services package

Rate me:
Please Sign up or sign in to vote.
4.54/5 (11 votes)
7 Apr 2008CPOL1 min read 227.3K   39   11
How to call a Web Service from a SQL Integration Services package.

Introduction

The SSIS framework provides a Web Service task which executes a Web Service method. You can use the Web Service task for the following purposes:

  • Writing to a variable the values that a Web Service method returns.
  • Writing to a file the values that a Web Service method returns.

Image1.jpg

This article defines a step by step approach to using a Web Service call in SQL Integration Services.

First, create a web service project.

image2.jpg

Figure 2 - Create a Web Service

Now, expose a method call in the Web Service:

C#
public class SSISService : System.Web.Services.WebService
{
    [WebMethod]
    public string HelloMoon()
    {
        return "Hello Moon";
    }
    [WebMethod]
    public int Multiply(int nParam1, int nParam2)
    {
        return nParam1 * nParam2;
    }
}

Create an SSIS package:

image3.jpg

Figure 3 - Create an SSIS Package

Add a web service task:

image4.jpg

Figure 4 - SSIS Toolbox

image5.jpg

Figure 5 - Web Service Task

Next, modify the Web Service task:

image6.jpg

Figure 6 - Edit Web Service Task

image7.jpg

Figure 7 - Web Service Task Editor

Now, define the HTTP connection:

image8.jpg

Figure 8 - HTTP Connection Manager Editor

The next step is to define the WSDL file:

image9.jpg

Figure 9 - Web Service Task Edit (Definition of the WSDL file)

Define the Web Service Task inputs:

image10.jpg

Figure 10 - Web Service Task Editor (Definition of web service input properties)

Now, define the Web Service output. The output of the Web Service can be written to variables or to an output file. This sample outputs the results from the Web Service to a file system file that is defined using the File Connection Manager Editor (Figure 11).

image11.jpg

Figure 11 - Web Service Task Editor (Definition of Web Service output properties)

image12.jpg

Figure 12 - File Connection Manger

Results

The following is the encoded output from the Web Service, stored in a test.txt file:

XML
<?xml version="1.0" encoding="utf-16"?>
<int>200</int>

References

  1. Web Service Task, SQL Server 2005 Books Online

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
Malta Malta
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Questionsuper Pin
dnanetwork4-May-15 2:14
dnanetwork4-May-15 2:14 
GeneralMy vote of 5 Pin
wsc09189-Jan-13 14:59
wsc09189-Jan-13 14:59 
GeneralMy vote of 5 Pin
Kanasz Robert25-Sep-12 22:47
professionalKanasz Robert25-Sep-12 22:47 
Questionhow this work if the parameters int in web service method Pin
yogesh r choudhary28-May-12 4:01
yogesh r choudhary28-May-12 4:01 
Generalin case of 403 error Pin
search002-Dec-09 9:57
search002-Dec-09 9:57 
GeneralCalling a Web Service from a SQL Integration Services package Pin
kulkarni anil 105-May-09 10:15
kulkarni anil 105-May-09 10:15 
GeneralRe: Calling a Web Service from a SQL Integration Services package Pin
Lokesh Lal25-Aug-09 18:56
Lokesh Lal25-Aug-09 18:56 
GeneralJust one more step Pin
Mycroft Holmes14-Apr-08 17:51
professionalMycroft Holmes14-Apr-08 17:51 
GeneralRe: Just one more step Pin
#realJSOP30-Aug-11 5:04
mve#realJSOP30-Aug-11 5:04 
Generalcool Pin
Niiiissssshhhhhuuuuu7-Apr-08 9:00
Niiiissssshhhhhuuuuu7-Apr-08 9:00 
GeneralThis is actually helpful Pin
defwebserver7-Apr-08 5:14
defwebserver7-Apr-08 5:14 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.