Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS Nugget: Ensure your root folder is valid

In the past I have advocated (and still do advocate) the use of a user variable in SSIS packages to store the path to a folder on the hard drive; I always use a variable called @[User::RootFolder] for this. Having this path available within a package variable enables you to dynamically set the location of files that your package may reference such as other packages, raw files, source files, log files etc..

Typically the value of this variable would get set by a configuration or the /SET option of dtexec however you do run the risk that someone might supply a path that does not have a backslash on the end of it and if you have expressions in your package that assume the presence of a backslash then you're going to get an error somewhere along the way.

There's an easy prevention to this problem of course. In addition to @[User::RootFolder] I also have a variable called @[User::ValidRootFolder]. The value of this variable is set dynamically using the following expression:

  • RIGHT(@[User::RootFolder], 1) == "\\" ?  @[User::RootFolder] :  @[User::RootFolder] + "\\"

What this expression basically says is:

IF (rightmost character of RootFolder is a backslash)
     return RootFolder
     return RootFolder concatenated with a backslash

Its a very simple check to put into your packages that can save some heartache down the line. In fact, why not make things even easier on yourself and put @[User::ValidRootFolder] into your template package?

Of course, this wouldn't be necassary if @[System::PackageLocation] existed.

Comments are welcomed.


P.S. Matthew Roche has a similar discussion here.

Published Monday, May 19, 2008 11:57 AM by jamie.thomson



Kaushal said:

Where would I define this root folder variable- I don't think it is the variables window... Because when I do that, It does not evaluate the expression.

June 11, 2008 9:43 PM

Kaushal said:

Ok got it, after visiting the Matthew Roche's discussion. BTW Jamie: Thanks for publishing this awesome blog! Very very helpful.

June 11, 2008 10:17 PM

Nick said:

Just thought I would mention a mistake I have made a few times - an RTRIM statement should be used in case the @[User::RootFolder] variable includes a trailing space.  This type of problem caused me no amount of consternation in troubleshooting a package one week.

so the amended expressions would be something like:

RIGHT(RTRIM(@[User::RootFolder]), 1) == "\\" ?  @[User::RootFolder] :  @[User::RootFolder] + "\\"


My thanks and appreciation as well for an excellent, well-maintained resource, Jamie.  Mucho kudos to you and yours.

June 23, 2008 5:05 PM
New Comments to this post are disabled

This Blog


Powered by Community Server (Personal Edition), by Telligent Systems