Click here to Skip to main content
Click here to Skip to main content

Calling a Web Service from a SQL Integration Services package

, 7 Apr 2008 CPOL
Rate this:
Please Sign up or sign in to vote.
How to call a Web Service from a SQL Integration Services package.


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.


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

First, create a web service project.


Figure 2 - Create a Web Service

Now, expose a method call in the Web Service:

public class SSISService : System.Web.Services.WebService
    public string HelloMoon()
        return "Hello Moon";
    public int Multiply(int nParam1, int nParam2)
        return nParam1 * nParam2;

Create an SSIS package:


Figure 3 - Create an SSIS Package

Add a web service task:


Figure 4 - SSIS Toolbox


Figure 5 - Web Service Task

Next, modify the Web Service task:


Figure 6 - Edit Web Service Task


Figure 7 - Web Service Task Editor

Now, define the HTTP connection:


Figure 8 - HTTP Connection Manager Editor

The next step is to define the WSDL file:


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

Define the Web Service Task inputs:


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


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


Figure 12 - File Connection Manger


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

<?xml version="1.0" encoding="utf-16"?>


  1. Web Service Task, SQL Server 2005 Books Online


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


About the Author

Simon Bonello
Malta Malta
No Biography provided

Comments and Discussions

GeneralMy vote of 5 Pinmemberwsc09189-Jan-13 15:59 
GeneralMy vote of 5 PinmvpKanasz Robert25-Sep-12 23:47 
Questionhow this work if the parameters int in web service method Pinmemberyogesh r choudhary28-May-12 5:01 
Generalin case of 403 error Pinmembersearch002-Dec-09 10:57 
GeneralCalling a Web Service from a SQL Integration Services package Pinmemberkulkarni anil 105-May-09 11:15 
GeneralRe: Calling a Web Service from a SQL Integration Services package PinmemberLokesh Lal25-Aug-09 19:56 
GeneralJust one more step PinmemberMycroft Holmes14-Apr-08 18:51 
a thought - how to get the data from the web service into a Table.
If the web service output is an XML recordset then the it could be stored into a variable and passed into a stored proc for processing into a table!
I can see I will need to go through this article in detail - thanks.
Never underestimate the power of human stupidity

GeneralRe: Just one more step PinmvpJohn Simmons / outlaw programmer30-Aug-11 6:04 
Generalcool PinmemberNiiiissssshhhhhuuuuu7-Apr-08 10:00 
GeneralThis is actually helpful Pinmemberdefwebserver7-Apr-08 6:14 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150224.1 | Last Updated 7 Apr 2008
Article Copyright 2008 by Simon Bonello
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid