Errors locating SSIS XML package configuration files.

SSIS provides several ways to make a package configurable, including using database configurations, XML file configurations, and environment variables. Of the different methods, XML files are probably the simplest and most dynamic approaches. However, it can be frustrating to figure out why a package generates errors that “The configuration file cannot be found” or that it “Failed to load at least one of the configuration entries for the package.” Why can’t it locate the configuration file? The key is to understand how SSIS sets what I will call the ‘working folder’ (that’s my term, it’s not in SSIS anywhere).

Before discussing that, let me clarify the situation a bit. When you create an XML configuration file, if you specify the path to the file, then that path will be required on all development machines as well as the production machines. On a recent project, the client didn’t want to have a fixed path for the SSIS files, instead they wanted the packages to accept configuration from wherever the package files were located. So, by design we did NOT specify a folder location for the configuration files. Instead, we simply specified the file name so that the configuration file would ‘live’ in the same folder as package. This worked fine, however it revealed an unexpected behavior of SSIS – this notion of a ‘working folder’.

Question: What determines where SSIS will look for its files? Well, that depends on how you opened your project. Some people launch BIDS from the Start menu and then select the project from the ‘Recent Projects’ list or choose ‘File – Open -- Project/Solution’. If you use either of those methods, then the ‘working directory’ will be wherever the BIDS executables reside, most likely something like ‘C:\Program Files\Microsoft Visual Studio 8\Common7\IDE’. I’m guessing that this is NOT where you put your configuration files, so as a result, when you open a package, you get an error message that the configuration files can’t be found. There’s really a simple solution to this and it is to 1) locate your configuration files (dtsconfig files) in the same directory as your solution file (.sln file), and then to 2) ALWAYS open your solution by double-clicking the solution file (.sln file). This will set the ‘working folder’ to be where the solution lives, your configuration file will be read correctly, and the errors about not finding the configuration should be gone.

Now, that works fine when you are developing, because you are specifying where the files open from, but what about production? How do you specify the ‘working folder’ when scheduling the packages using SQL Agent? Well, we ran into that situation as well, and sure enough when SQL Agent tries to execute a package, its ‘working folder’ defaults to the location of the DTEXEC executable. So, a simple workaround was to create a batch file that first changes to the directory where the packages and configuration files live, and then execute DTEXEC with the command line options that we wanted. Then, in SQL Agent, instead of using the ‘SQL Server Integration Services package’ type job, use an ‘Operating System (CmdExec)’ type job and just call the batch file. Worked like a charm. If the location of the packages ever needs to change to a different folder, drive, or even server, it’s a simple change to the batch file to reference that new location.

In closing, having a fixed configuration location or using an environment variable to hard-code the location of configuration files would avoid this situation, but places requirements on server configuration and directory structure. SSIS can support completely dynamic configuration file location, but you need to be aware of how it sets its ‘working folder’.