Posting data to a WCF Service from a SSIS Data Flow Destination (and debug it)

Currently I’m working on a system that needs to convert different bulk files into single messages. We want to use SSIS to import the files, transform the records into xml messages and send those messages to BizTalk. I’m new to SSIS so I decided to do some little ‘Proof of Concepts’. This post is about calling a (WCF) Service from the Data Flow.

I created a new solution, added an Integration Services project and a Class Library to hold the code. Using a library to hold the code has several advantages, like maintainability and of course write code in C#. The Script Components use Visual Basic for Applications, that’s bad enough. Anyhow, I now add a Data Source and a Script Component, type Destination, to the Data Flow.

Because we need to reference the Class Library in the Scrip Component, I added two commands to the Post-build Events of the Class Library project, one is to register the dll in the GAC (Sign it!), the other so that the SSIS Script Designer can find it:

“C:Program filesMicrosoft Visual Studio 8SDKv2.0BinGacUtil.exe” -i “$(TargetPath)”
Copy “$(TargetDir)$(TargetName).dll” “C:Program FilesMicrosoft SQL Server90SDKAssemblies”

I also added a Data Viewer between de Data Source and the Script Component, this gives you time to Attach the debugger to the SSIS process (DtsDebugHost.exe). I’ll now edit the code of the Script Component so it calls my own class:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Omvormer.Components

Public Class ScriptMain
    Inherits UserComponent

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        Dim wcf As WCFDestination = New WCFDestination()
        wcf.ProcessInputRow(Row.Data)
    End Sub

End Class

The Class Library project I make a reference to my WCF Service. Visual Studio automatically adds a proxy class I can use in my code. Now I implement the code to call the Service in my class, not too exciting:

namespace Omvormer.Components
{
    public class WCFDestination
    {
        public WCFDestination() { }

        public void ProcessInputRow(string messageText)
        {
            Omvormer.Components.TGSBToegang.RouteerBerichtClient rbc = new Omvormer.Components.TGSBToegang.RouteerBerichtClient();
            rbc.Open();
            rbc.Routeer(messageText);
            rbc.Close();
        }
    }
}

Ready? No not really, if you start the package now you’ll get an error like this:

‘Could not find default endpoint element that references contract ’XXXXX’ in
the ServiceModel client configuration section. This might be because no
configuration file was found for your application, or because no endpoint
element matching this contract could be found in the client element.’

When you referenced the Service it also created an app.config file containing some configuration for this Service. You’ll have to add this to dtexec.exe.config and/or dtsdebughost.config (Located in your application directory, e.g. C:Program FilesMicrosoft SQL Server90DTSBinn), so the config will look like this:

<configuration>
    <startup>
        <requiredRuntime version="v2.0.50727"/>
    </startup>
    <system.serviceModel>
        <bindings>
            <!-- Yadda Yadda -->
    </bindings>
    <client>
        <!-- Yadda Yadda -->
    </client>
</system.serviceModel>
</configuration>

(Thanks to this post on SSIS Junkie)
Ok that’s it! Feel free to comment, correct me or whatever you can’t resist to post.