Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS: How to use .Net assembly configuration files in the script task

A couple of work colleagues had a problem when using SSIS yesterday which took a bit of working out so I thought I'd share it here, it'll be useful for anyone else that has the same problem.

It was all to do with calling .Net assemblies (i.e. DLLs) from the SSIS Script Task. The assembly in question was basically a proxy to a web service however it took its configuration (i.e. the location of the web service) from a configuration file. The problem was that we couldn't figure out where to put the configuration file in order that the .Net engine within SSIS could locate it and use it. Hence, whenever we ran the script task we got this problem:

 

Just for the benefit of anyone searching for this on the web, here's the text in that message box:

Could not find default endpoint element that references contract 'contract name' 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.

Eventually the guys managed to find the solution. As many of you will surely know the executable that runs SSIS is dtexec.exe which, if you accept the default installation, you can find at %ProgramFiles%\Microsoft SQL Server\90\DTS\Binn. In there you'll also find a file called dtexec.exe.config which invariably looks like this:

<configuration>
  <startup>
    <requiredRuntime version="v2.0.50727"/>
  </startup>
</configuration>

Now, someone who knows more about .Net than I do can probably give you a textbook definition* of what that file does (I guess it'll talk about the CLR and AppDomains and similar such stuff) but essentially all we had to do was place the configuration for our assembly into this file like so:

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

and hey presto, it worked!

Don't forget that you'll also need to make the same change to dtsdebughost.exe.config if you're running such a package from BIDS and also to dtshost.exe.config if your package is being called via the Execute Package Task in another package which is called from BIDS (read that slowly a few times - it'll make sense eventually).

 

Hope this helps!

-Jamie

*If anyone can furnish me with a textbook definition then please stick it in the comments section below.

Published Friday, January 25, 2008 11:59 PM by jamie.thomson

Comments

 

John Saunders said:

Unfortunately, I suspect that this changes the configuration options for every script task or other piece of .NET code that runs from inside of SSIS.

Also unfortunately, I don't know the solution to that problem.

January 26, 2008 8:23 PM
 

john.rayner said:

Jamie,

You should be able to add a config alongside your deployed assembly.  So assuming your assembly is "C:\Foo\ProdmlWCFClient.dll", you would then create "C:\Foo\ProdmlWCFClient.dll.config".  The format of this is identical to the usual xxx.exe.config files.  I think this is a neater solution and it will ease deployment slightly (always a good thing!).

Cheers,

John

January 27, 2008 11:38 PM
 

jamie.thomson said:

John,

We tried that and it didn't work - hence the dialog box above and subsequent sustained head scratching.

-Jamie

January 28, 2008 3:42 AM
 

john.rayner said:

Jamie,

You could also try running Process Monitor (http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx) to find out where SSIS is looking for config files.  This will give you the directories and file names which get probed.

Cheers,

John

January 29, 2008 12:21 AM
 

Amine said:

John your my hero !

I used the process monitor to find which config file SSIS use and that worked well !!

There's three days that I am looking for a solution to that issue !

So big big thank you

March 5, 2008 2:11 PM
 

Dave said:

Does anyone have any specifics around what config files that SSIS looks for when executing .net component from a script task? I am currently developing my bits and would not like to have to wait until executing via script task to address this item.

Thanks

March 12, 2008 4:38 PM
 

TrackBack said:

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

April 8, 2008 9:08 PM
 

Troy Hall said:

FYI: if you are debugging, you need to add the config to DtsDebugHost.exe.config.

May 5, 2008 10:10 PM
 

Tom said:

on debug enviroment its running fine, but when I deploy the package to integration service server. Its seem like it not executing the configuration at all.

I put my configuration in DTExe.exe.config and dtshost.exe.config on the server.

anyone have any idea how to make it work ?

thanks

August 20, 2008 4:13 PM
 

Luis said:

It worked for me.  Thanks man!

November 14, 2008 12:12 AM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems