Here are a few tricks I learned while developing SQL Server Integration Services packages.

Set a relative path to your dtsConfig file.

This makes it much easier to execute your package with different configuration files. By default SSIS will try to import the configuration file from the path originally used to create it – which is often the Development server.

To set a relative path to your config file:

1. First create the config file, in the same folder as your package.
2. Close the package in the SSIS designer.
3. Right-click the package and select View Code, to view the XML source.
4. Find the configuration file entry. You can search for the filename, or you can search for the string “DTS:config”.
5. Look for the element named <DTS:Property DTS:Name=“ObjectName”>. The value will be the path and filename to the configuration file.
6. Edit this string to be just the filename alone, and save the package XML.

From now on: open the solution by double-clicking the .SLN file from Windows Explorer. The config file is relative not to the package file, but to the Visual Studio IDE current directory. If you double-click the SLN file, Windows sets the current directory to the folder where the SLN file is. This means to keep it simple, keep your SLN file, Package file, and dtsConfig file in the same folder. If you want to split them into separate folders, remember to make your path in the package XML relative to the SLN file, not the Package file – note I have not tested this. 🙂