In SQL Server 2008 Integration Services, I’m trying to write a package that will consume a web service by sending an XML Soap request containing recordID and receiving XML containing details about that record. I have a working Perl script but one of the requirements is to deploy this in SSIS.
Excerpts from some documentation I found:
it would need to post a valid SOAP request, not just do an HTTP get. Both the request and response messages will be wrapped within a SOAP envelope and sent over HTTP. HTTPS
The web service communicates via the HTTPS protocol, which supports synchronous transactions and encrypted requests. Simple Object Access Protocol (SOAP) is used for message formatting, which allows for the description of the web service using a Web Service Description Language (WSDL) file. SOAP-enabled clients will be able to take advantage of these technologies to speed up and simplify the development process.
WSLx Authentication
All applications accessing the web service must authenticate via WSLx by including a encrypted persistant cookie string in their HTTPS request. This encrypted string contains the WSLx username for the client application, the source IP address of the request, and an expiration date. This information is decrypted and validated by a WSLx server agent running on the web servers.
I attempted using the Web Service task unsuccessfully. To see if the WS task even works, I attempted using the Web Service task with a public web service that returns address information when you submit a zip code, and it was successful, so I know it works under certain circumstances.
I tried this in a Script Task, my code (sensitivity replaced by ellipsi):
Dim uriString = “https://web…servlet”
Dim request As HttpWebRequest = CType(WebRequest.Create(uriString), HttpWebRequest)
Dim responsetext As String = “”
Dim reader As IO.StreamReader
reader = New IO.StreamReader(request.GetResponse().GetResponseStream())
request.CookieContainer = New CookieContainer()
request.Method = “POST”
request.ContentType = “text/xml”
Dim response As HttpWebResponse = CType(request.GetResponse(), HttpWebResponse)
response.Headers.Add(“Cookie”, “WSLX-credential=Sjo…(264 character string) …TsLZI0Q”)
responsetext = reader.ReadToEnd()
Dts.Events.FireInformation(0, “responsetext=”, responsetext, “”, 0, fireAgain:=1)
I expect this code block to return XML with all the details of the recordID that I submit, but it just returns the html of the login page. This uses the same URI and cookie string as the working PERL script so I know those are correct. attached Perl script (sensitivity replaced by ellipsi).
What am I missing?
