Archive

Archive for July, 2010

Web Service Task in SSIS 2005, WSDL Issue fix

I ran into this issue yesterday. Here is the scenario,
 
SSIS needs to call a web service to send & receive data. You place web service task on SSIS designer and create a HTTP connection manager. I specified web service path in HTTP Connection Manager and WSDL file path in Web service task. Every thing worked on my dev machine. When i moved the SSIS on QA SSIS server. I started getting following error message 

  Code: 0xC002F304
   Source: Log Start Message to Process Manager Web Service Web Service Task
   Description: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: The input Web Services Description Language (WSDL) file is not valid.
   at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil..ctor(Object connection, String downloadedWSDL)
   at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()".
End Error
DTExec: The package execution returned DTSER_FAILURE (1).

 
As error is stating that it is not able to get WSDL so gave me a hint that it always need WSDL file even if you specify ?WSDL in HTTP Connect Manager for web service URL.  Customer was running SSIS through DTEXEC from file system and with a config file. so i figured out the way to export WSDLFILE  path in config so that we can configure from server to server. I created a variable with the name WSDLFILE_PATH and exported it to config.
 
Select the Web service Task and go to expressions. Expressions provide you different properties available. so it has WSDLFILE property. I simply added expression to get variable value. Thats served the purpose of providing WSDL file path from config file. Since i could not find any article stating the correct solution to resolve it so i decided to blog it. I hope you found it useful. My experience is that SSIS 2005, you can easily use a web service task. Some people say that you have limitations in 2005 and web service task never works etc. Let me tell you that this is not true. I tested web service task extensively in 3 different SSIS packages and every thing worked very well.
 
Categories: SSIS 2005