How to Configure an SSIS Package to Access a Web Service using WCF


When you are connecting to a web service from an SSIS Script component or transform using a WCF client, the normal method of configuring the WCF client from the application configuration file doesn’t work well.  Your package will be running in some host, like dtexec.exe, and you would have to put your client config its application configuration file.  SSIS packages have their own way of consuming configuration, by either setting package variables (or other settings) from the command line, or using an external package configuration (stored in a SQL Server table or XML file).  To use the normal SSIS configuration mechanisms to configure a WCF client, the easiest way is to configure your WCF client in code, and plug in package variables for things like the service URL, or perhaps the client credentials or proxy settings.  If you configure the WCF client in code using this technique then you don’t have to edit the dtexec.exe.config file.

Here’s a quick walkthrough of calling a Web Servcie from a WCF client in an SSIS package, configuring the WCF client in code and using a package variable for the web service URL.

First create a WCF Service for testing (or use some existing web service):

image

then hit F5 to run the service in the development server, and note the URL of the .svc file:

image

Create a new SSIS package and add a DataFlow and a Script Component configured to Source.  Add output columns to the script source to match the data flowing out of the web service.  Here it’s just a single integer.

image

Edit the script for the script source and change the target .NET Framework from 2.0 to 3.5 so you can use WCF:

image

In the Script project add a Service reference:

image

Enter the URL of your web service, and give it a friendly name:

image

 

 

 

 

 

Create a Package variable to configure the service URL, and give the script source read-only access to the variable.

image

Now edit the script to configure the WCF client in code and pass in the package variable for the URL. 

/* Microsoft SQL Server Integration Services Script Component
*  Write scripts using Microsoft Visual C# 2008.
*  ScriptMain is the entry point class of the script.*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.ServiceModel;
using SC_e9f00fa5cbb748d4b5b80e10f6c61d98.csproj.MyService;


[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

    ChannelFactory<IService1> channelFactory;
    IService1 client;
    public override void PreExecute()
    {
        base.PreExecute();

        //create the binding
        var binding = new WSHttpBinding();
        //configure the binding
        binding.Security.Mode = SecurityMode.Message;
        binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Windows;

        var endpointAddress = new EndpointAddress(this.Variables.ServiceUrl);
        channelFactory = new ChannelFactory<IService1>(binding, endpointAddress);

        //create the channel
        client = channelFactory.CreateChannel();
    }

    public override void PostExecute()
    {
        base.PostExecute();

        //close the channel
        IClientChannel channel = (IClientChannel)client;
        channel.Close();

        //close the ChannelFactory
        channelFactory.Close();

    }

    public override void CreateNewOutputRows()
    {

        for (int i = 0; i < 10; i++)
        {
            this.Output0Buffer.AddRow();
            CompositeType t = new CompositeType();
            t.BoolValue = false;
            t.StringValue = i.ToString();

            var data = client.GetDataUsingDataContract(t);
            this.Output0Buffer.id = data.BoolValue?1:0;
        }
        
    }

}

If you’re not sure what binding to use look at the app.config that the Add Service Reference wizard put in your script project.  It will tell you.  Here it requires wsHttpBinding with Message transport security and a Windows credential:

image

Add a simple data flow destination and test your package:

image

Then you can set the value of the package variable on the command line with dtexec.exe or SQL Agent, or create a package configuration that has sets them. 

David


Comments (14)

  1. h3ly says:

    Hi David,

    Great post! But I still have a doubt on this. With this approach, I don't need to edit the DTExec.exe.config file?

    Thanks in advance.

  2. dbrowne says:

    Correct.  The WCF channel is configured in code instead of the config file.

  3. Sergio Carreño says:

    Hi David

    thanks a lot for this solution!

  4. Nilesh Bhandarwar says:

    Can you please provide working package for this functionality

  5. satish says:

    Script Component :Runtime Error

    Project name: SC_d186e8035fad4a5bb9383970cdb69ffb

    Object reference not set to an instance of an object.

    Please help how to solve this error

  6. Chitova says:

    Dont be so stupid, that the reference of his workspace, just replace that with your own workspace.

  7. aakash says:

    I have to use certificate. Can anybody post c# code for it.

  8. YikWing says:

    Hi David,

      ChannelFactory<IService1> channelFactory;

       IService1 client;

    Where does the IService1 come from?

    Thanks.

  9. ljvargas says:

    Thank you

  10. Jim says:

    Where is the CompositeType defined?

  11. JS says:

    I have the same problem with: Object reference not set to an instance of an object, i'm using my own workspace, the script c is Transformation, because I need some data to call service

  12. NigelB says:

    Great thanks. Just to answer some previous questions:

    "IService1" is the interface to the service you want.

       ChannelFactory<IMyService> channelFactory;

       IMyService client;

    Now, as soon as you type "client." intellisense should show you the available methods from the service. Pass the values of parameters into the script using package variables like "ServiceUrl" above. Then use these for the method parameters.

    To process a results set (as opposed to 1 returned record), use a foreach loop.

           string abbrevText = Variables.AbbrevText;

           string verCodeText = Variables.VerCodeText;

           int lengthDesc;

           int lengthDescCode;

           var data = client.GetMyData(abbrevText, verCodeText);

           foreach (var datarec in data)

           {

               OutputBuffer.AddRow();

               lengthDesc = datarec.DescText.Length >= 50 ? 50 : datarec.DescText.Length;

               lengthDescCode = datarec.Code.Length >= 50 ? 50 : datarec.Code.Length;

               OutputBuffer.Descriptor = datarec.DescText.Substring(0, lengthDescr);

               OutputBuffer.DescriptorCode = datarec.Code.Substring(0, lengthDescCode);

           }

    Remember to inject the required bindings in PreExecute() and decide whether you need BasicHttp/WSHttp-Binding:

           var binding = new BasicHttpBinding();

           //configure the binding

           binding.Security.Mode = BasicHttpSecurityMode.None;

           binding.MaxBufferSize = 2147483647;

           binding.MaxReceivedMessageSize = 2147483647;

           binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Windows;

    GOOD LUCK :)

  13. wilson smyth says:

    im still unclear whtere IService1 is comming from.

    As mentioned, its the interface to the service, but i cant see where its declared anywhere, in the service or anywhere else.

  14. wilson smyth says:

    ignore me, i figured it out! Thanks, good tutorial