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):

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

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.

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

In the Script project add a Service reference:

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

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

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:

Add a simple data flow destination and test your package:

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