I have a SSIS 2008 script task. I have added a Web Reference to a SOAP webservice on the script editor and have the following code to instantiate the service and call a method
MyWebService.Service ox = new MyWebService.Service();
string sResult;
sResult = ox.MyMethod();
everything works fine when this code runs in BIDS but fails with the following error when run from the SQL server Agent:
Executed as user: My_Domain\SQLServerAgentServiceAccount. Microsoft (R) SQL Server Execute Package Utility Version 10.50.6000.34 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 2:51:44 PM Error: 2015-08-24 14:52:08.99 Code: 0x00000001 Source: Script Task Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond xxx.xx.x.xx:443 at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress) at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP) at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception) --- End of inner exception stack trace --- at System.Net.HttpWebRequest.GetRequestStream(TransportContext& context) at System.Net.HttpWebRequest.GetRequestStream() at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) at ST_4c5d20bf332741e2882cde151f073447.csproj.MyWebService.Service() at ST_4c5d20bf332741e2882cde151f073447.csproj.ScriptMain.Main() --- End of inner exception stack trace --- at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript() End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:51:44 PM Finished: 2:52:09 PM Elapsed: 24.672 seconds. The package execution failed. The step failed.
I have xxx.xxd out the IP address in the above error message. what's surprisingly when using BIDS and monitoring the network flow with wireshark the xxx.xxd out IP address is never being hit while when monitoring the flow of the agent the xxx.xxd IP address is being hit but a failure is generated. Furthermore when going the BIDS Route looks like HTTP is used to get to the vendors webservice URL. While when going through the agent wireshark indicates that the first thing in the TCP stream is a TCP call to the vendors IP.
Note that BIDS and Agent are running on the same server
Any one have any idea what the problem could be?
Thanks in advance
