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

Comments

• Anonymous
  November 12, 2010
  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.

• Anonymous
  December 20, 2010
  Correct.  The WCF channel is configured in code instead of the config file.

• Anonymous
  June 06, 2013
  Hi David thanks a lot for this solution!

• Anonymous
  September 05, 2013
  Can you please provide working package for this functionality

• Anonymous
  May 08, 2014
  The comment has been removed

• Anonymous
  August 05, 2014
  Dont be so stupid, that the reference of his workspace, just replace that with your own workspace.

• Anonymous
  January 21, 2015
  I have to use certificate. Can anybody post c# code for it.

• Anonymous
  March 12, 2015
  Hi David,   ChannelFactory<IService1> channelFactory;    IService1 client; Where does the IService1 come from? Thanks.

• Anonymous
  March 24, 2015
  Thank you

• Anonymous
  May 01, 2015
  Where is the CompositeType defined?

• Anonymous
  July 20, 2015
  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

• Anonymous
  August 06, 2015
  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 :)

• Anonymous
  September 23, 2015
  The comment has been removed

• Anonymous
  September 23, 2015
  ignore me, i figured it out! Thanks, good tutorial