title: SSIS Cannot consume web service
body: <p>I'm using SSIS to connect to a Cisco web service hosted on the Cisco machine that manages our telephones. I've tested the service in SoapUI and have found everything to be working. I can get/use the WSDL, can connect to the service using authentication credentials, get an expected reply.</p>
  
  <p>I first tried to use a Web Service Task. I set up my Http Connection Manager, selected it to use credentials, and ran the test. The test completed successfully. I downloaded the wsdl and saved it locally. In the Web Service Task I point to the wsdl file and the service and methods are detected in the input pane. But when I execute the task, I get the following exception:</p>
  
  <p>Error: 0xC002F304 at Web Service Task, Web Service Task: 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 underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel..
     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()".</p>
  
  <p>Again, my Http connection test passed, and I’ve retyped my credentials that enabled me to successfully test with SoapUI several times now.</p>
  
  <p>After being unable to resolve the issue with a Web Service Task, I tried using a Script Task. I set up a HttpWebRequest and set all the header properties the same way they were while testing in SoapUI. Also, I set the Authorization: Basic value to correct value. I then built the data String that is passed to a StreamWriter exactly the way it was for the SOAP Envelope in SoapUI. The StreamWriter Writes and then Closes, but when I call GetResponse, I get the wonderful exception of:</p>
  
  <p>Warning: 0x0 at Script Task 1, WEB EXCEPTION CAUGHT: : The server committed a protocol violation. Section=ResponseStatusLine
  Error: 0x6 at Script Task 1: The script returned a failure result.</p>
  
  <p>For this issue I’ve tried</p>
  
  <ul>
  <li><p>Setting ProtocolVersion to 1.0 and then tried 1.1</p></li>
  <li><p>Setting KeepAlive to false</p></li>
  <li><p>Setting ServicePoint.Expect100Continue = false;</p></li>
  <li><p>Tried to programmatically set useUnsafeHeaderParsing to true but the script would not compile even though the editor did not indicate any errors (may have been caused by reflection, I don’t know)</p></li>
  <li><p>Don’t know how to access the configs in my SSIS package to set useUnsafeHeaderParsing to true that way</p></li>
  </ul>
  
  <p>None of these worked. It doesn’t matter which task I use, I just need one to work. Anybody have any ideas/suggestions or can point me toward anything else I can use to research these issues? (I’ve been Googling for two days now :(  ).</p>
  
  <p>Below is a snippet of my code from my script in case anyone else can see an issue I’m overlooking. Thank you for your time and reading my question!</p>
  
  <pre><code>        StreamReader responseReader = null;
          StreamWriter requestWriter = null;
  
          string data = getPayload();
  
          HttpWebRequest httpRequest = (HttpWebRequest)WebRequest.Create("http://***********:8443/realtimeservice2/services/RISService");
          httpRequest.ProtocolVersion = System.Net.HttpVersion.Version11;
          httpRequest.Method = "POST";
          httpRequest.AutomaticDecompression = DecompressionMethods.Deflate | DecompressionMethods.GZip;
          httpRequest.ContentType = "text/xml;charset=UTF-8";
          httpRequest.Accept = "Accept: text/*";
          httpRequest.Headers.Add("SOAPAction", "executeCCMSQLStatement");
          httpRequest.Headers.Add("Authorization", "Basic ********");
          httpRequest.ContentLength = data.Length; 
          httpRequest.Host = "*********:8443";
          httpRequest.KeepAlive = false;
          httpRequest.UserAgent = "Apache-HttpClient/4.1.1 (java 1.5)";
          requestWriter = new StreamWriter(httpRequest.GetRequestStream(), System.Text.Encoding.ASCII);
  
          requestWriter.Write(data);
          requestWriter.Close();
  
          WebResponse webResponse = httpRequest.GetResponse();//EXCEPTION THROWN
  </code></pre>
  
  <p><strong>UPDATE</strong></p>
  
  <p>So we’ve tried installing the service host’s certificate into the trusted root store of the machine making the call. The connection manager can now test an https successfully, but when I run the package I still get the same ‘Could not establish trust relationship for the SSL/TLS’ issue. My desktop can still successfully connect to the service, make the call, and get back expected results. But for some reason I cannot get the DB server and the service host to play nice together.</p>