I need to test using web services as the data source in a SSIS package. As I do not have any web services ready already, I have divided my task into two:
1. Create a web service that exposes data
2. Use that web service as the data source and use Web services task, HTTP connection manager to read the data.
Now I know that No 1 is not possible using SQL server as there is a known bug. Any of you have tried out using any other web services as data source for SSIS?
I tried using some freely available web services in http://www.xmethods.net/ but the http connection manager could not talk to those. Ideally this should be the scenario where we use any web service as the source. I am getting the error 'can not connect to the remote server' when I type in these in the Server URL http://www.xmethods.net/sd/2001/CurrencyExchangeService?wsdl
This is a valid wsdl as I can type out in the browser.. also it gives me correct output when I tried using thru their website. Anybody tried this scenario?
I was able to use the service you named successfully.
My connection manager had the server url: http://www.xmethods.net/sd/2001/CurrencyExchangeService?wsdl
My Web Service task had the following configuration:
WSDLFile: c:\mywsdl.wsdl (I clicked the "Download WSDL" button)