Where is my app.config for SSIS?

by Darren Green 3 Aug 2009 09:20

Sometimes when working with SSIS you need to add or change settings in the .NET application configuration file, which can be a bit confusing when you are building a SSIS package not an application. First of all lets review a couple of examples where you may need to do this.

  • You are using referencing an assembly in a Script Task that uses Enterprise Library (aka EntLib), so you need to add the relevant configuration sections and settings, perhaps for the logging application block.
  • You are using using Enterprise Library in a custom task or component, and again you need to add the relevant configuration sections and settings.
  • You are using a web service with Microsoft Web Services Enhancements (WSE) 3.0 and hosting the proxy in SSIS, in an assembly used by your package, and need to add the configuration sections and settings.
  • You need to change behaviours of the .NET framework which can be influenced by a configuration file, such as the System.Net.Mail default SMTP settings. Perhaps you wish to configure System.Net and the httpWebRequest header for parsing unsafe header (useUnsafeHeaderParsing), which will change the way the HTTP Connection manager behaves.
  • You are consuming a WCF service and wish to specify the endpoint in configuration.

There are no doubt plenty more examples but each of these requires us to identify the correct configuration file and and make the relevant changes. There are actually several configuration files, each used by a different execution host depending on how you are working with the SSIS package.

The folders we need to look in will actually vary depending on the version of SQL Server as well as the processor architecture, but most are all what we can call the Binn folder. The SQL Server 2005 Binn folder is at C:\Program Files\Microsoft SQL Server\90\DTS\Binn\, compared to C:\Program Files\Microsoft SQL Server\100\DTS\Binn\ for SQL Server 2008. If you are on a 64-bit machine then you will see C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\ for the 32-bit executables and C:\Program Files\Microsoft SQL Server\90\DTS\Binn\ for 64-bit, so be sure to check all relevant locations. Of course SQL Server 2008 may have a C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\ on a 64-bit machine too.

To recap, the version of SQL Server determines if you look in the 90 or 100 sub-folder under SQL Server in Program Files (C:\Program Files\Microsoft SQL Server\nn\) . If you are running a 64-bit operating system then you will have two instances program files, C:\Program Files (x86)\ for 32-bit and  C:\Program Files\ for 64-bit. You may wish to check both depending on what you are doing, but this is covered more under each section below.

There are a total of five specific configuration files that you may need to change, each one is detailed below:

DTExec.exe.config

DTExec.exe is the standalone command line tool used for executing SSIS packages, and therefore it is an execution host with an app.config file.

e.g. C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe.config

The file can be found in both the 32-bit and 64-bit Binn folders.

DtsDebugHost.exe.config

DtsDebugHost.exe is the execution host used by Business Intelligence Development Studio (BIDS) / Visual Studio when executing a package from the designer in debug mode, which is the default behaviour.

e.g. C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DtsDebugHost.exe.config

The file can be found in both the 32-bit and 64-bit Binn folders. This may surprise some people as Visual Studio is only 32-bit, but thankfully the debugger supports both. This can be set in the project properties, see the Run64BitRuntime property (true or false) in the Debugging pane of the Project Properties.

dtshost.exe.config

dtshost.exe is the execution host used by what I think of as the built-in features of SQL Server such as SQL Server Agent

e.g. C:\Program Files\Microsoft SQL Server\90\DTS\Binn\dtshost.exe.config

This file can be found in both the 32-bit and 64-bit Binn folders

devenv.exe.config

Something slightly different is devenv.exe which is Visual Studio. This configuration file may also need changing if you need a feature at design-time such as in a Task Editor or Connection Manager editor.

  • Visual Studio 2005 for SQL Server 2005  - C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\devenv.exe.config
  • Visual Studio 2008 for SQL Server 2008  - C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe.config

Visual Studio is only available for 32-bit so on a 64-bit machine you will have to look in C:\Program Files (x86)\ only.

DTExecUI.exe.config

The DTExec UI tool can also have a configuration file and these cab be found under the Tools folders for SQL Sever as shown below.

  • C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\DTExecUI.exe
  • C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\DTExecUI.exe

A configuration file may not exist, but if you can find the matching executable you know you are in the right place so can go ahead and add a new file yourself.

In summary we have covered the assembly configuration files for all of the standard methods of building and running a SSIS package, but obviously if you are working programmatically you will need to make the relevant modifications to your program’s app.config as well.

Comments (9) -

8/12/2009 5:53:35 PM #

sam

We have SQL server running on IA64 box. Where i can find devenv.exe?

sam United States

8/16/2009 9:36:41 AM #

Darren Green

Sam, Visual Studio does not support WOW64 for IA64, you cannot install it, hence no devenv.exe can ever exist. In other words you cannot develop SSIS on IA64, so there is no app.config to change.

Darren Green United Kingdom

9/24/2009 8:07:29 PM #

Joe

How do you dynamically specify dtexec.exe.config file? I saw the /conf switch and tried dtexec.exe /conf "C:\mypath\app.config" but that didn't seem to actually read the config file. Everything works if I edit the original in the binn directory as described in your post. That doesn't seem like a viable solution when you have multiple packages that all need different configurations. Basically what I need is to read in different app.config files for script components in different packages, so for example the dev and production config files will be slightly different. Changing the binn directory is a global change and doesn't seem like a good way to do it. Ideas?

Joe

9/25/2009 4:37:27 PM #

Darren Green

Joe, you may be confusing a .NET Application Configuration (msdn.microsoft.com/en-us/library/ms229689.aspx) with SSIS Package Configurations (msdn.microsoft.com/en-us/library/ms141682.aspx). This post is about the former, and there is no way to dynamically set a .NET app.config file. The dtexec /config option is for setting a SSIS package configuration files. As a rule I suggest you avoid using .NET application configurations in SSIS, best practice would suggest that all parameter and settings should be managed via SSIS.

Darren Green United Kingdom

10/1/2009 10:38:51 PM #

Jyotin Shah

Darren,

Thanks a bunch for this post.

What's intreseting is that Microsoft document this (at least I couldn't find one)
Even worst, if you add a Service Reference, it adds/appands App.config to temp project.

Jyotin Shah United States

2/15/2010 11:44:45 PM #

Nathan Griffiths

Hi Darren,

When using a script component to call a WSE 3.0 web service, where would I put the wse3policyCache.config file? My package builds correctly but at runtime I get an error about the policy not being found.

thanks, Nathan

Nathan Griffiths New Zealand

2/17/2010 11:41:29 AM #

Darren Green

Nathan, I have not used it myself, but I'd find the correct app.config as above, then add the policy element (msdn.microsoft.com/en-us/library/aa528786.aspx) to that file. Put wse3policyCache.config in the same directory as the app.config.

Cheers

Darren Green United Kingdom

3/30/2011 5:46:19 PM #

Megan Brooks

It is possible to add App.config to a task in SSIS. I checked this for a data flow script component, but something similar should work in the control flow. As noted above, App.config is added automatically when you add certain references. You can also, while editing a script, right-click the project name (a GUID by default) in the Project Explorer, add a new item, and select Application Configuration File.

I had a little trouble getting the new file to "stick" within the project. When I closed and reopened the script editor the file disappeared from the project explorer. I clicked "Show all files" and "Include in project" to bring it back and, just for good measure, added a line of code to the main script that references ConfigurationSettings.AppSettings. After that, the file "stuck" within the project.

I haven't tried to actually DO anything with this file yet, but the fact that it seems to work for Web References combined with the fact that you can add your own App.config to a script component task is promising.

Megan Brooks United States

4/8/2011 2:36:32 PM #

Valentino Vranken

Very useful post Darren!  I encountered your article while searching for a way to add configuration to a Script task.  My task uses a custom assembly and that assembly has got a config file to specify the binding for a web service that it's using.  Using the info here I was able to get it to work.

However, I'm not in favor of spreading the binding config all over those different files.  As .NET config files unfortunately don't seem to have a way to "import" another config file by way of an absolute path reference, I'm now trying to figure out how to configure the binding through code.  As far as I can tell that should work too.

Best regards,
Valentino.

Valentino Vranken Belgium

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

RecentComments

Comment RSS
SQLIS | Presentations

RecentComments

Comment RSS