We have chosen to use relative paths in Integration Services on several projects now, and since it has worked well, it is time to share this tip with the community. The whole purpose of using relative paths is to make packages more portable, as opposed to packages and dtsConfig files having to live in a specific directory structure or drive letter on servers and developers’ laptops. As evidenced by the volume of posts in the forums about problems with deploying SSIS packages, anything that makes packages more portable will make deployment less prone to error.
When I say “relative paths in SSIS” I mean the following:
- .dtsx files and .dtsConfig files are all inside one directory
- Execute Package tasks refer to the package to execute using a File connection manager, and that connection manager refers to the .dtsx file with a relative path (i.e. just the filename)
- Package Configurations are of type “XML configuration file” and the path to the .dtsConfig file is just a relative path (i.e. just the filename)
- dtexec executes packages from disk, not from SQL Server or from the SSIS package store
- When dtexec is run, the working directory has to be set to the directory with all your files. This is accomplished by running dtexec from a .bat file which uses the CD command to set the working directory.
- When designing packages, Visual Studio must be started with the appropriate working directory which is the directory with all the packages. This can be accomplished by double clicking the .sln file if it is in the same directory as the packages. Or you can write a .bat file that runs the CD command to change the working directory before it starts Visual Studio.
When setting up an Execute Package task, mimic the following screenshots to use a relative path:
When setting up a package configuration, use the relative path:
Unfortunately, in SSIS 2008, this Package Configuration Wizard screen does not allow you to enter a plain relative path due to a bug. However, there is a BIDS Helper feature which allows you to workaround this bug.
Setting Working Directory
An example .bat file which runs dtexec with the correct working directory would look like the following. The first line changes to the drive of the working directory. The second line actually changes to that directory. The third line runs dtexec.
D: cd "D:\MyPackages\" dtexec /FILE "master.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
When setting up a SQL Agent job step, you cannot use the “SQL Server Integration Services Package” job step type in a SQL Agent job since it does not let you set the working directory. But you can use the “Operating System (CmdExec)” job step type which lets you run the .bat file. (To go further, some will argue here and here that you should always use the “Operating System (CmdExec)” even when you’re not needing to set the working directory for relative paths.) To run this bat file, set the “command” property to the full path to the bat file in quotes:
An example .bat file which runs Visual Studio with the correct working directory would look like the following. The first line changes to the drive of the working directory. The second line actually changes to that directory. The third line runs dtexec.
D: cd "D:\MyPackages\" "C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\devenv.exe" "D:\MyPackages\test.sln"
If you forget and start Visual Studio from the Start Menu (such that the working directory is not set to your packages directory), the .dtsConfig file it creates for you from your relative path will end up in the “C:\Program Files\Microsoft Visual Studio 8\Common7\IDE” directory.
Adding .dtsConfig Files to Your Visual Studio Solution
You can include .dtsConfig files in your solution in Visual Studio. Move the .dtsConfig out of the directory with your .dtsx files, then in Windows Explorer copy the .dtsConfig file to your clipboard, then left click on the Miscellaneous folder in Solution Explorer in Visual Studio, then type Ctrl-V to paste it into your SSIS project. The .dtsConfig file is now in the same directory as your .dtsx files (which is important for relative paths, as discussed above). And your .dtsConfig file can now be checked into source control with your .dtsx files. And the .dtsConfig file will be readily accessible in Visual Studio. Opening that .dtsConfig by double clicking it, you can pretty-print the XML by pressing Ctrl-K then Ctrl-D. (Or if you have BIDS Helper installed, it will automatically pretty-print it for you upon opening it.)
As a side note, I should mention in this context that whenever I’m working with package configurations, I use the Expression and Configuration Highlighter feature of BIDS Helper which gives you a little teal triangle on anything controlled by a package configuration. I always look to see that every connection manager is controlled by an expression or a configuration.
This relative paths strategy has saved us some headaches several times when moving the deployment destination for packages to different environments. So I would recommend you consider using the relative paths trick when deciding on the deployment and configuration strategy for your ETL project.