Relative paths in SSIS to make things simpler

Moving packages just got easy.

I worked on a large project last year at a global company that sells household items, and one thing that I liked about the project was the fact that they used ‘relative’ paths for their SSIS packages when referencing resources, such as text files, executables, xml files, etc. If you have used these before, this is old-hat and probably not terribly interesting; if not, it’s pretty slick!

Notice in the below test package that I have a Flat File Connection – FF_Source1 – and it’s wired-up to a file as seen in the below picture:

This is great, but what if I want to deploy this to the server?  Chances are, you’re probably not going to land the data file in the C:\My Documents\Visual Studio 2008\Projects\Examples\Data_Files folder. Rather, it might be in some other folder on another drive, maybe E:\SSIS\Projects\Data_Files for instance. And that’s only for the dev box. What if it has to be pushed to four or five servers, and maybe they’re all set up differently. Maybe Dev has an E:\ drive, but Validation or Test has an L:\ drive where the packages are stored. Sounds like a mess, eh?

Thank goodness for relative paths. Rather than the entire UNC path name, let’s just put the following in there - add this to your data source:  ..\Data_Files\Source1.txt

Niiiiiiiice!  How does this work? It’s easier explained by looking first at the document out on the Microsoft MSDN site, and search for “Paths”. Here are the excerpts from the link:

• Use a period as a directory component in a path to represent the current directory, for example ".\temp.txt". For more information, see Paths.
• Use two consecutive periods (..) as a directory component in a path to represent the parent of the current directory, for example "..\temp.txt". For more information, see Paths.
• A path is also said to be relative if it contains "double-dots"; that is, two periods together in one component of the path. This special specifier is used to denote the directory above the current directory, otherwise known as the "parent directory". Examples of this format are as follows:
• "..\tmp.txt" specifies a file named tmp.txt located in the parent of the current directory.
• "..\..\tmp.txt" specifies a file that is two directories above the current directory.
• "..\tempdir\tmp.txt" specifies a file named tmp.txt located in a directory named tempdir that is a peer directory to the current directory.

 

So, .\ is the current path, ..\ is the parent of the current directory, and ..\..\ is two directories up from where your package sits...relative to it's location.  This requires that you create a set of folders that you will push onto all of your boxes (at the company where I am consulting now they have five (5) environments) so that when you move a package from one box to another, it knows where to look, regardless of the letter drive. Sweet!

Check it out and let me know how it works for you.

Lee

-------------------------

It's not that I'm lazy; it's just that I don't care

Refs: http://msdn.microsoft.com/en-us/library/aa365247(VS.85).aspx#paths

Visit: