SSIS uses absolute paths to point to objects on the file system. What does that mean? In short it means that in order to point to a file I would have to use the full absolute path, I couldn't give a path that is relative to where the packages resides.
A common complaint about SSIS is that it does not allow the use of relative paths. The most useful application of this feature would be the ability to execute packages (using the Execute Package Task) where all the packages are (as is typical) stored in a single folder and there have been many people complaining about the absence of this feature over on the SSIS forum. I do not know why relative paths are not supported in SSIS but I suspect that it is because packages do not always reside in the file system, sometimes they reside in SQL Server's msdb database and in this instance there is no location for a file path to be relative to.
Due to the reason just stated I have always agreed that stipulating the use of absolute paths within SSIS was the right thing to do (and indeed I have championed it) however of late I have changed my mind. Support for relative paths would greatly simplify package deployment and package management and for this reason I have made the following submission to Connect:
A common complaint about SSIS is that file references cannot be relative to wherever the package resides. Relative paths would greatly simplify package deployment and management, especially when using the Execute Package Task.
Please introduce a new system variable called [System::PackageLocation] (or similar) that contains the location of the package on the file system. This variable could be used (in conjunction with BLOCKED EXPRESSION to provide support for relative paths.
A developer would be able to deploy all of his/her packages to the same location and be confident that through the use of [System::PackageLocation] the Execute Package Task could always easily find the packages that it was executing.
[This is in addition to a number of existing submissions for similar functionality.]
If you require similar functionality today then you can ape the [System::PackageLocation] behaviour that I just described by telling the package where it is when it executes. I describe a strategy for doing this in my blog entry Common Folder Structure from January 2006.
What do you think? Should SSIS support relative paths? (That's probably a rhetorical question but I'll ask it anyway.)
UPDATE, 14th July 2008: Greg Galloway has posted an alternative method to leveraging relative paths in SSIS2005. Read it here: http://www.artisconsulting.com/Default.aspx?tabid=94&EntryID=9