none
Access WebService VIA SSIS Script Component

    Question

  • I am trying to access a Web Serive via the SSIS Script Component and facing Issues as per screenprint below.

    a) The Webservice is WCF WEbservice so i am adding it as a Service Reference and accessing it

    ServiceName request = new Service();        
    ReportClient client = new ReportPortClient("Report");

    client.Endpoint.Address = new EndpointAddress("<URL>");
    client.ClientCredentials.UserName.UserName = "XYZ";
    client.ClientCredentials.UserName.Password = "XYZ";

    b) I have tried the same code as above and am able to create a Datable via a CONSOLE APP.

    c) I am using CUSTOM BINDING to access the WEBSERVICE

    I feel the error is that SSIS Script task is not able to find the somehow the app.config file which is generated once you add the service refernce, the error says that it cant find the contract which is defined on the config file. THis error is raise as soon as i start the DFT>

    d) I have palced a break point on the preexute task, and that is also not hit, this error comes before hand

    Has someone faced the same issue and have any resolution for the same.

    Again i feel the SSIS task is not finding the APP.config file does someone know any resolution for this


    Abhinav
    http://bishtabhinav.wordpress.com/


    Wednesday, April 10, 2013 12:33 AM
    Moderator

Answers

  • Hey josst thanks for the reply, yes i have saved it, and now able to get it working.

    The problem was the SCRIPT COMPONENT task doesnt recongnise any config file, and when we add the Service reference it stores the Contract name and binding details on the app.config file.

    The workaround was to create the HTTPBINDING( this is supported by my Webservice) from code, i created the contract and provided the bindings from code, instead of depending on the APP.config. Now everything is working well.

     The below post spoke about SSIS Script component not supporting App.config which was validated by my findings as well, posting my code if it might help someone connect via

    BASICHTTP binding.

    http://msdn.microsoft.com/en-us/library/ff647110.aspx  

     BasicHttpBinding binding = new BasicHttpBinding(BasicHttpSecurityMode.TransportWithMessageCredential);
            binding.Security.Message.ClientCredentialType = BasicHttpMessageCredentialType.UserName;
            binding.MaxBufferSize = 2147483647;
            binding.MaxBufferPoolSize = 2147483647;
            binding.MaxReceivedMessageSize = 2147483647;

            // Replace <machinename> with the name of the development server.
            EndpointAddress remoteAddress = new EndpointAddress("<service Link>");

            ReportPortClient client = new ReportPortClient(binding, remoteAddress);
            client.ClientCredentials.UserName.UserName = "USER NAME";
            client.ClientCredentials.UserName.Password = "Password";

            BindingElementCollection elements = client.Endpoint.Binding.CreateBindingElements();
            elements.Find<SecurityBindingElement>().IncludeTimestamp = false;
            client.Endpoint.Binding = new CustomBinding(elements);

    // THis portion is specific to my call of web service

           Execute_ReportType request = new Execute_ReportType();
           response = client.Execute_Report(request);

    Got the idea from this post which used the

    NETTCP binding,

    http://www.bibits.co/post/2012/08/15/Consuming-Web-Services-in-SSIS-Script-Component.aspx


    Abhinav
    http://bishtabhinav.wordpress.com/

    • Marked as answer by AB82Moderator Wednesday, April 10, 2013 4:27 PM
    Wednesday, April 10, 2013 4:27 PM
    Moderator

All replies

  • Just checking: Have you pressed "Save all" after adding the webservice and before closing the Script Component editor?

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Wednesday, April 10, 2013 11:15 AM
    Moderator
  • Hey josst thanks for the reply, yes i have saved it, and now able to get it working.

    The problem was the SCRIPT COMPONENT task doesnt recongnise any config file, and when we add the Service reference it stores the Contract name and binding details on the app.config file.

    The workaround was to create the HTTPBINDING( this is supported by my Webservice) from code, i created the contract and provided the bindings from code, instead of depending on the APP.config. Now everything is working well.

     The below post spoke about SSIS Script component not supporting App.config which was validated by my findings as well, posting my code if it might help someone connect via

    BASICHTTP binding.

    http://msdn.microsoft.com/en-us/library/ff647110.aspx  

     BasicHttpBinding binding = new BasicHttpBinding(BasicHttpSecurityMode.TransportWithMessageCredential);
            binding.Security.Message.ClientCredentialType = BasicHttpMessageCredentialType.UserName;
            binding.MaxBufferSize = 2147483647;
            binding.MaxBufferPoolSize = 2147483647;
            binding.MaxReceivedMessageSize = 2147483647;

            // Replace <machinename> with the name of the development server.
            EndpointAddress remoteAddress = new EndpointAddress("<service Link>");

            ReportPortClient client = new ReportPortClient(binding, remoteAddress);
            client.ClientCredentials.UserName.UserName = "USER NAME";
            client.ClientCredentials.UserName.Password = "Password";

            BindingElementCollection elements = client.Endpoint.Binding.CreateBindingElements();
            elements.Find<SecurityBindingElement>().IncludeTimestamp = false;
            client.Endpoint.Binding = new CustomBinding(elements);

    // THis portion is specific to my call of web service

           Execute_ReportType request = new Execute_ReportType();
           response = client.Execute_Report(request);

    Got the idea from this post which used the

    NETTCP binding,

    http://www.bibits.co/post/2012/08/15/Consuming-Web-Services-in-SSIS-Script-Component.aspx


    Abhinav
    http://bishtabhinav.wordpress.com/

    • Marked as answer by AB82Moderator Wednesday, April 10, 2013 4:27 PM
    Wednesday, April 10, 2013 4:27 PM
    Moderator
  • Thank you for sharing your solution!

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Wednesday, April 10, 2013 4:40 PM
    Moderator