SSIS Script Task Web Service error

Question

I have a script task in a SSIS package to call a web service (WCF). All I am trying to do is to send a request to the web service and I'll receive a result (either 1 or 0). However if failed with the following error message. When I put the code in a window app form, it worked though.

I pretty much copied the code from a window application and trying to make it work in SSIS. Unfortunately I have no idea how web service works and not sure where to start to look for a solution. Do I need to do any binding? There's an app config file but it says CustomBinding. Again, I apologize for my lack of knowledge, I'm totally clueless. Any help would be appreciated. Thanks!

at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) 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 Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

Following is my code in the script task.

public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

    public void Main()
    {
        // TODO: Add your code here                

        ServiceReference.SendMessageClient svc = new ServiceReference.SendMessageClient();


        MessageCredentialsHeader MessageSecurityHeader = new MessageCredentialsHeader("user", "pw");
        try
        {
            using (System.ServiceModel.OperationContextScope contextScope = new System.ServiceModel.OperationContextScope(svc.InnerChannel))
            {
                System.ServiceModel.OperationContext.Current.OutgoingMessageHeaders.Add(MessageSecurityHeader);

                MessageBox.Show(svc.SendMessage("111", "222", "SSIS test").Result.ToString());

            }
        }
        catch (Exception ex)
        {
            string s = ex.Message;
        }

        Dts.TaskResult = (int)ScriptResults.Success;
    }


    enum ScriptResults
    {
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    };
    #endregion
    public class MessageCredentialsHeader: System.ServiceModel.Channels.MessageHeader
    {

        public string Username { get; set; }
        public string Password { get; set; }

        public MessageCredentialsHeader(string username, string password)
        {
            Username = username;
            Password = password;
        }

        public override string Name
        {
            get { return "Security"; }
        }

        public override string Namespace
        {
            get { return "http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"; }
        }

        public override bool MustUnderstand
        {
            get
            {
                return true;
            }
        }

        protected override void OnWriteStartHeader(XmlDictionaryWriter writer, System.ServiceModel.Channels.MessageVersion messageVersion)
        {
            base.OnWriteStartHeader(writer, messageVersion);
            string prefix = writer.LookupPrefix("http://schemas.xmlsoap.org/soap/envelope/");
            writer.WriteAttributeString(prefix + ":actor", "http://example.org/ws/webservicesecurity");
        }

        protected override void OnWriteHeaderContents(System.Xml.XmlDictionaryWriter writer, System.ServiceModel.Channels.MessageVersion messageVersion)
        {
            writer.WriteStartElement("UsernameToken", Namespace);
            writer.WriteStartElement("Username", Namespace);
            writer.WriteRaw(Username);
            writer.WriteEndElement();
            writer.WriteStartElement("Password", Namespace);
            writer.WriteAttributeString("Type", "http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText");
            writer.WriteRaw(Password);
            writer.WriteEndElement();
            writer.WriteEndElement();
        }
    }

}

Robert Anderson Robert Anderson · Accepted Answer · 2014-08-01T02:09:52

you can pass in a readwrite parameter to your script task - e.g. securityResponse. You will need to first create a variable in your package, with a value of the xml response. Then, within your script code, you can add code such as this which may steer you in the right direction:

 object nativeObject = Dts.Connections["Webservice"].AcquireConnection(null);
 HttpClientConnection conn = new HttpClientConnection(nativeObject);

 Service ws = new Service(conn.ServerURL);
 securityRequest req = new securityRequest();
 req.username = "****";
 req.password = "****";

 securityResponse response = ws.GetSecurityResp(req);

 System.Xml.Serialization.XmlSerializer x = new System.Xml.Serialization.XmlSerializer(response.GetType());
 StringWriterWithEncoding responseToXml = new StringWriterWithEncoding(new StringBuilder(), Encoding.UTF8);

 x.Serialize(responseToXml, response);
 Dts.Variables["User::securityResponse"].Value = responseToXml.ToString();
 Dts.TaskResult = (int)ScriptResults.Success;

You will then need to create another data flow task with an XML source that reads this XML and places it either to a recordset destination or table.

SSIS Script Task Web Service error

2 Answers