Friday, March 2, 2012

SSIS Timeout to a Web Service with Web Service Task

Quick Description:  The WebService task in SSIS throws an error similar to the one below, but the return code from the actual Web Service is 200.

The error:

[Web Service Task] Error: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: The Web Service threw an error during method execution. The error is: The operation has timed out.
   at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebMethodInvokerProxy.InvokeMethod(DTSWebMethodInfo methodInfo, String serviceName, Object connection)
   at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil.Invoke(DTSWebMethodInfo methodInfo, String serviceName, Object connection, VariableDispenser taskVariableDispenser)
   at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()".

If you have access to the WebService being called (or have written a proxy), the logs show that the service itself returned a successful status code 200.

This is misleading (or at least I thought it could be worded better).  It can mean that the WebService task  itself, rather than the WebService being called timed out.    This is made even more confusing by the fact the timeout is set not on the WebService task, but on the httpConnectionManager.  Default is 30 seconds.  Large amounts of data can take longer than 30 seconds to return without being a problem.  As far as I understand 300 seconds is the limit, which doesn’t seem long enough to me.

Timeout can be increased (up to 300 seconds) in Connection Managers,  yourHttpConnectionManager, properties, timeout.

1 comment: