posted 4/29/2010 by BillBrannen - Views: [72018]
I have been exploring the various tasks available in SSIS and thought I would put together a tutorial on the Web Service Task. Using this task is pretty straightforward, but some of the settings are not too clear. Here is a step-by-step guide.
1. Create a package-level variable named StockSymbol with a data type of String and a value of MSFT.
2. Add a new connection manager by right-clicking the Connection Managers window and selecting New Connection.
3. When prompted for the type, select HTTP and click the Add button. This opens the HTTP Connection Manager Editor.
4. Enter the location of the web service WSDL file in the Server URL field. In this case, we are calling a web service that returns a stock quote.
5. Click the Test Connection button. If the test succeeds, click Ok to close the editor window.
6. Next, add a File Connection Manager. In the editor window, select Create File for Usage type and enter a file name of Output.xml in your chosen directory. Click Ok to close the editor window.
7. The Connection Managers window should resemble the following.
8. Next, add a Web Service Task to the package; open the task editor and select the General page.
9. Under the Connection group, set the HTTPConnection option by selecting the HTTP Connection Manager that was created earlier.
10. Then set the WSDLFile value by clicking the ellipsis button and navigating to the local copy of the web service’s WSDL file. If you have not already downloaded the WSDL file manually, you can create an empty file and set the OverwriteWSDLFile option to True and click the Download WSDL button.
11. Next, select the Input page and set the Service option by selecting StockQuote in the drop-down list.
12. Set the Method option by selecting GetQuote in the drop-down list.
13. After selecting a Method, the option to enter a value is displayed. Select the Variable check box and select the User::StockSymbol variable that was created earlier. Also make sure Type is String.
14. Next, select the Output page and set the OutputType to File Connection.
15. For the File option, select the file connection manager which was created earlier.
16. Click the Ok button to close editor.
17. Run the package and view the Output.xml file. The results should be similar to the following.
<?xml version="1.0" encoding="utf-16" ?> <string><StockQuotes><Stock><Symbol>MSFT</Symbol><Last>30.875</Last><Date>4/29/2010</Date><Time>11:19am</Time><Change>-0.035</Change><Open>30.96</Open><High>30.97</High><Low>30.67</Low><Volume>17750884</Volume><MktCap>270.6B</MktCap><PreviousClose>30.91</PreviousClose><PercentageChange>-0.11%</PercentageChange><AnnRange>19.01 - 31.58</AnnRange><Earns>1.93</Earns><P-E>16.02</P-E><Name>Microsoft Corpora</Name></Stock></StockQuotes></string>
For more information, see the Web Service Task topic in SQL Server 2008 Books Online.
hi,
good article, i want to do much more add-ons such as from the xml file i want to read data and store into database,
can you give some idea about that
Trying to use this with devvirualearth but it gives me a mesage : "the selected web method contains unsupported arguments"???
Hi, my 5 cents...I use web services very intensively for collect data from Lotus, 1C (it's a ERP- system)) and several other internal and external systems. In most cases, web service returns a small amount of data. In the database I have identified schemes and procedures with validated XML parameters. I use the variable for store "Web services task" output and then I use the resulting XML as input parameter to a stored procedure. SP does all other works uses excellent features of SQL SERVER 2008. :) If webservice returns a lot of data or method defined in webservice uses non-English languages (Russian in my case) - I use ScriptTask instead of Webservice TaskAlso i use "Web services task" for send data into webservices
I follow your guide to get data from web site. When I press "Input" in Web Service Task Editor, the following error is shown:
TITLE: Web Service Task
------------------------------
Could not read the Web Services Description Language (WSDL) file. The input WSDL file is not valid. The following error occurred while reading the file. There is an error in XML document (1, 1)..
BUTTONS:
OK
Could you please tell me why it is?
Is there a way to upload a file to https using a web task?
Great tutorial. Would love to see an example of calling a web service that takes a ComplexType (xml) as a parameter.
I created a HTTP connection Manager and I am trying to select the Service from the Input screen. I can see my service listed (its a CRMService). But when I try to select it, throws a GPF. Any Ideas?
I am usign SQL Server BI 2008