Calling a Web Service from an SQL Integration Services package

 clipped from www.codeproject.com

Calling a Web Service from an SQL Integration Services package

---

Introduction

The SSIS framework provides the 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 use web service call in SQL integration services.

• Create a web service project

Figure 2 - Create Web Service

• Expose method call in the web service

    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 SSIS package

Figure 3 - Create SSIS Package

• Add web service task

Figure 4 - SSIS Toolbox

Figure 5 - Web Service Task

• Modify the web service task

Figure 6 - Edit Web Service Task

Figure 7 - Web Service Task Editor

• Define the HTTP Connection.

Figure 8 - HTTP Connection Manager Editor

• Define the WSDL File

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

• Define the Web Service Task Inputs

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

• Define the Web Service Output

The output of the web service can be written to variables or 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

• Result

The following is the encoded output from the web service stored in the test.txt file.

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

<int>200</int>

References

[1] Web Service Task, SQL Server 2005 Books Online, http://msdn2.microsoft.com/en-us/library/ms140114.aspx

License

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

Get Clipmarks - The easiest way to email text, images and videos you find on the web.