How to call a BizTalk Orchestration exposed as a Web Service from SSIS?
In a recent implementation at a client I was stunned on how limited the Web Service Task in SSIS really is. No matter how hard I tried SSIS could simply not call the BizTalk Web Services. Information on the topic seem to be non-existent and this is something I thought was done on a daily basis.
A few days later after I pulled nearly all my hair out I decided to write VB.NET code in the Script Task component to make the service call for me. This may not be the right way, others may prefer using an itinerary service of some sort or maybe to write a wrapper in C# but for this implementation the Script task did just fine. Here’s how I did it:
-
Drop a Script Task component in your SSIS package designer.
-
Open the script editor and include a reference to Interop.MSXML2. I copied this file to the C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 directory.
-
Use the following code to make the Web Service call in the Sub Main segment:
1 2 3 4 5 6 7 8 9 10 11 12 13
Dim Http As New MSXML2.XMLHTTP30() On Error GoTo Err Http.open("POST", "http://localhost/MyBizTalkWebService/Orch/Receive.asmx", False, "", "") Http.setRequestHeader("SOAPAction", "http://MyCompany/StartInterface/Operation_1") Http.setRequestHeader("Content-Type", "text/xml") Http.send("<?xml version='1.0' encoding='utf-8'?><soap:Envelope... (xml soap message goes here)") Http = Nothing Dts.TaskResult = Dts.Results.Success Exit Sub Err: Http = Nothing Dts.TaskResult = Dts.Results.Failure
There are some pros and cons to this approach. One pro is that you can make dynamic calls to web services by reading the SOAP properties from a database or configuration file. One con is that you have to construct the XML soap messages manually that can be difficult with complex messages.
