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:

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:

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:

(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.

Please rate this

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

  1. dan

    For those using SSDT 2015, the file location for .config files has shifted when using the debugger; they can now be found under the following path: C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SSIS\140\Binn

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *