Let me start by saying that I prefer SQL Server configurations over all other configuration types in SSIS for the majority of configurable values in my SSIS projects because they are more secure, easier to decipher, and fit well into database deployment methods and tools that I use.
Unfortunately, not all configurations can be of the SQL Server configuration type as we need an initial configuration that stores the connection string for the database that the SQL Server configurations live in.
This is where it becomes necessary to use an XML configuration file type of SSIS configuration. One of the problems with using an XML config file is that SSIS does not understand relative paths. This means that all environments must store the config file in the exact same directory structure, otherwise the file will not be found (except in some circumstances which I'll explain later in this post).
To get around this problem we can use something called an Indirect XML configuration file configuration type to store this single configuration containing the connection string for the SSIS configuration database. See the first configuration in the screen shot below.
The beauty of this configuration type is that it allows you to use an XML config file in such a way that its location is not environment specific. It does this by getting the location of the XML config file from a Windows Environment Variable. Unfortunately this must be created in environments where SSIS packages are being developed (where BIDS is installed) and in runtime environments where the config file does not live in the same directory as the SSIS packages being executed. While this does require added effort, it only has to be done once per environment. Below is a screen shot of the creation of a sample Environment Variable.
If you store your XML configuration file (.dtsconfig) in the same directory as the rest of your SSIS packages (.dtsx) then it is not necessary to create this environment variable on machines where your packages will be executed by the SSIS runtime and/or dtexec/dtexecui. SSIS will attempt to find the Environment Variable and when it cannot, it will then look in the root path of the SSIS packages and use the config file found there.
However, if your design requires that you store your XML config file in a location other than your package location you must create the Environment Variable in all locations.
At the risk of repeating myself, BIDS will not be able to find your config file unless you hard-code it's absolute path in a regular XML configuration file configuration (non-Indirect XML configuration) or create an Indirect XML configuration using an Environment Variable. This is true even if the config file lives in the same directory as your packages. This works fine when there is only one developer and that developer uses only machine to develop. Once you have multiple development team members with potentially different directory structures, you can avoid many headaches by making your XML config file location environment agnostic. Don't fear the Environment Variable!
Hi David,
ReplyDeleteThanks for sharing such a valuable information. This helped me a lot. Keep up the good work.
Regards,
Sri.
Hi David,
ReplyDeleteI am trying to set my package configuration as per your suggestion.
My understanding was that if the environment variable for the indirect xml configuration was not found, SSIS would use the hardcoded value in the package.
So in order to test this I deleted the environment variable on my machine for an existing package which I had set up with xml indirect location. When running it via dtexec afterwards I could see the warning message saying it couldn't find the environment variable. However although I had the .dtsconfig with the same file name copied into the package folder, SSIS ignored that and used the connection string hard coded value in the package.
This made me realise: If the environment variable is not present, how would the package know the .dtsconfig file name to look for? Unless I am missing something here, in the xml indirect configuration the .dtsconfig full path, including the file name, is detailed in the environment variable.
I would really appreciate if you could share further details on how you have set this up.
Many thanks in advance!
Vinnie
Vinnie, if you're executing the package via dtexec you must be calling dtexec from a command prompt at your config file's location. For example...
Deletec:\SSIS>C:\Program Files\Microsoft SQL Server\100\DTS\Binn\dtexec -f MyPackage.dtsx
Note that I call dtexec while in the C:\SSIS directory which is where my packages and dtsconfig file would be.
Let me know if that doesn't work.
Pity you can't just use "\" or "\config" and be done with it.
ReplyDeleteIn adding existing packages exported from MSDB to a BIDS project I can see the hardcoded config file location is all in a "Y" drive directory. Y indeed I asked myself. It does make sense though because the SSIS server has been replicated in a few countries and Y would be a fairly unlikely drive letter so they could quite easily create Y partitions and replicate.
I like the environment variable workaround though! Much better idea.
Is this what you'd recommend?:
Keep the config file in the same directory as the dts packages
Create environment variable on all dev machines
Not worry about creating the environment variable on the machines where your packages will be executed