Partager via

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

  • Article

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

  • 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