Issue with Web Services Task in SSIS

  • Article

Recently I've observed a new issue with Web Services Task in SSIS (2005 build). The Web Service Task fails with:

<error>
[Web Service Task] Error: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: Could not execute the Web method. The error is: There was an error generating the XML document..    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()".
</error>

This happens only when you are trying to download large data sets using this task. If the dataset is few megs, the task works perfectly.

Analysis showed that the task is built using XMLSerialization object. XMLSerialization has been reportedly throwing OutOfMemoryException for larger datasets.

What are our options:

A] Set /3GB switch on the box which will have more memory available for the XMLserialization on your box. Caution: This may have negative impact on the performance of the OS depending on the amount of memory you have.

B] (recommended) Use the script task to write a small piece of code that will do the same work. If you need help with that, click here:
https://msdn2.microsoft.com/en-us/library/ms173022(VS.80).aspx
https://support.microsoft.com/kb/320438
https://msdn.microsoft.com/code/list/websrv.asp

C] Write a separate .NET exe application that does the same work.

D] Wait for the next release of SQL Server i.e. SQL Server 2008.