Consuming Data from Web Service using Script Task in SSIS or Web Service Task RRS feed

  • Question

  • Hi All,

    I need to extract the data from a WebService and then load the same into SQL Database.

    I have the following with me.

    a WSDL File, a XSD file, A certificate, username and password and the xml used to connect to the service.

    Below mentioned is the code to connect to service

        <wsse:Security xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" SOAP-ENV:mustUnderstand="0">
            <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">[PASSWORD]</wsse:Password>

    HttpWebRequest myXmlhttp = (HttpWebRequest)WebRequest.Create(myURL);
                myXmlhttp.Proxy = new WebProxy() { UseDefaultCredentials = true };
                myXmlhttp.Method = "POST";
                myXmlhttp.Headers.Add("SOAPAction", "urn:retrieveCoverDecision");
                myXmlhttp.ContentType = "text/xml;charset=UTF-8";
                byte[] requestBytes = System.Text.Encoding.ASCII.GetBytes(myXMlMessage);
                myXmlhttp.ContentLength = requestBytes.Length;
                Stream requestStream = myXmlhttp.GetRequestStream();
                requestStream.Write(requestBytes, 0, requestBytes.Length);
                HttpWebResponse res = (HttpWebResponse)myXmlhttp.GetResponse();
                StreamReader sr = new StreamReader(res.GetResponseStream(), System.Text.Encoding.Default);
                string backstr = sr.ReadToEnd();

    When my Code reaches

    Stream requestStream = myXmlhttp.GetRequestStream();

    Its throwing the below exception.

    .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
       at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
       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()

    When I hit the URL its opening in Internet Explorer.

    I don't know what wrong I am doing, how to use WSDL File or XSD file or a reference to it or how to use certificate if I have to use any. :(

    Please help

    Thanks & Regards,
    Manjunath C Bhat,

    Thursday, December 19, 2013 9:06 AM


  • Hi All,

    My Issue got resolved and now I am able to download the data I want.

    I sorted out it was the proxy which was troubling me

    I added below piece of code and the issue is now resolved.

    myXmlhttp.Proxy = new WebProxy() { UseDefaultCredentials = true };
                myXmlhttp.Proxy = WebRequest.DefaultWebProxy;
                myXmlhttp.Credentials = new NetworkCredential("myUserName", "myPassword", "myDomain");
                myXmlhttp.Proxy.Credentials = new NetworkCredential("myUserName", "myPassword", "myDomain");

    Thank you all

    Thanks & Regards,
    Manjunath C Bhat,

    Thursday, December 19, 2013 1:16 PM