Web service task in SSIS?

Today we will learn how to use web service task in SSIS. Before going into details about the task we will learn about web service to understand better

What is web service?

Web service is piece of information which can be shared between two devices through World Wide Web (WWW). This has an interface file WSDL (web service definition language) which is in machine readable format (XML) and the same is used to connect to consume the web methods provided by web service.

You can search more about web service in online books to know more about web service and WSDL

Web service task in SSIS is used to retrieve data in XML format by executing web methods in web service

Below mentioned steps will explain how to use web service task in SSIS

Step1: Drag and Drop Web service task from tool box on to control flow design editor as shown in figure 1

Figure 1

Step2: Web service task should be configured by double clicking the task which displays Web service task editor as shown in Figure 2.

Figure 2.

This task requires the establishment of an HTTP connection manager where web service is hosted or specific WSDL file is placed on website. If HTTP connection does not point to a WSDL file on site, Local version should be provided.

Click on HTTP connection tab which displays Http connection manager Editor where WSDL location should be placed. Credentials and Certificate should be provided if there are any attached to the web site. In this example I used web service which is hosted in localhost server as shown 3. Click on test connection link to verify your HTTP Connection.

You can configure the HTTP Connection Manager to access a Web services hosted on

www.webservicex.net, which is most commonly used for learning purpose.

www.webservicex.net/stockquote.asmx?WSDL is one such example to use stockquote web method which is hosted on web servicex.net

Figure 3.

Step3: Provide WSDL file for the web service. If WSDL does not exist you can download using Download WSDL option and OverWriteWSDLFile should be set either true or false based up on requirement as shown in Figure 2

Step4: The next step is to define input editor. Select the service which is used to execute web service and you need to specify the web method which you want to execute for the input of the task. Web service task will provide all the available methods by WSDL which is provided in general tab as shown in figure 4.

Once you select one of the web methods Add (Which takes two integer values and return addition of those two values as integer), the web service will display required Parameters for the task. You can provide hard-coded values or through variable as shown in figure 5.

Figure 4

Figure 5

Variable 1 = 10 and Variable2 = 20 values passed to Add method.

Step5: The remaining tab is output tab as shown in figure 6. The output of resulting web service method can be saved either variable, select a data type as string as output will be sent in XML format or File connection. In this example Output is saved in variable which is later printed using script task as shown in Figure 6.

Figure 6

Write below mentioned code in Script task which is to read data from Variable2 which is passed as ReadOnly variable in script task

System.Windows.Forms.MessageBox.Show(Dts.Variables["Variable2"].Value.ToString());

Running this web service task will result in calling the web method Add on the Service1 and retrieving the data in XML variable as shown in Figure 7

Figure 7

Technorati Tags: SSIS,MSBI,BI,Sql server,web service task,task