Monday, June 24, 2013

SSIS Web Service Task: 417 Expectation Failed

I had some bad time with SSIS web service task... our daily loads used to throw me an exception....

[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 request failed with HTTP status 417: Expectation Failed..
   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()".

I tried fighting with Fiddler, then it has thrown me some other Error like could not establish secure connection between the server..
at last Ghees Alias saved my life [http://blog.qburst.com/2013/05/configure-ssis-web-service-task-effectively/]

all we need to do is to add the below configuration in 
Go to C:\Program Files\Microsoft SQL Server\110\DTS\Binn. Here, you will find 2 files, namely DtsDebugHost.exe.config and DTExec.exe.config. You need to modify these two files. Open each file on Visual Studio. Copy-paste the following tags inside the configuration tag of each file.
   
     
   


The configuration files should now look like this:
img8


in normal C# coding in any script we can use the below line before we call our web method..
System.Net.ServicePointManager.Expect100Continue = false; 

Enjoy, it has started working fine.. woooooooh...

No comments:

Post a Comment