Web Service Task

The Web Service task is a new task in SSIS to connect to a Web service and execute a method. Once you execute the method, you can write the results from the Web service to a file or to a variable. This would be useful for trading information with third-party applications. For example, you can execute a Web service method to retrieve a list of updated products at Amazon. Then, you can write those products to a file and input them into your database to make them live.

To use the task, you'll need to open the Web Service Task Editor and specify a HTTP Connection Manager in the General tab. The HTTP Connection Manager looks quite a bit different from the OLE DB Connection Managers you're used to. In the Server URL option, point to the .ASMX file of the Web service. If you're just experimenting with the task and want a Web service to try, go to www.xmethods.net/ for a myriad of free Web services. Most won't require a key or authentication of any type. If the Web service does require credentials, then you'll need to modify the HTTP Connection Manager. The one being used in Figure 3-20 will extract the hyperlinks from a given Web page and would be handy for creating a package that can spider a Web site.

Figure 3-20

Once your HTTP Connection Manager is configured, go back to the General page to select the Web Service Description Language (WSDL) for the service. This file describes to the task how to use the service. If you do point to an external Web site, you must copy the WSDL file locally. On this page, you can also select whether the WSDL file can be overwritten.

The Web service you connect to may or may not require input parameters. Input parameters are passed to the method in the Input page of the task. In this page, you select the service you wish to call from a drop-down box in the Service option and then the method you'd like to execute from the Method drop-down box. The WebMethodDocumentation option will then automatically fill in to describe the method. The inputs will then appear at the bottom in the Service box. Type in the parameters you'd like to send to the service and go to the Output tab to configure where you'd like the results to be sent. You can have them sent to a file connection or to a variable. In the example in Figure 3-21, the Web service is sent a URL to spider, and the results are outputted to a variable, which is configured in the Output page. If you output to a file, you would use the File Connection Manager.

Figure 3-21