First published on MSDN on Jun 15, 2007
This was part of the solution to the
project
my team was working on (that I'm still in the process of writing up), but I thought I'd post it separately because it has various uses.
The Problem
We have a package which does some aggregations based on a date to generate a report. Normally we'd want to use the current date when the package is run, but we also want to support passing in a specific date on the command line.
The Issues
If SSIS variables supported NULL values, we'd be able to use an expression like this to set our value:
ISNULL( @[User::ReportDate] ) ? GETDATE() : @[User::ReportDate]
Here we leave the ReportDate variable with a NULL value at design time, so when the package is executed without parameters, we use the current date (given to us by the GETDATE() function). If ReportDate is set on the command line, we'd use that instead.
Since we can't use NULL, I thought I'd use the same logic, but with a hard coded value. If you drag in the NULL(DT_DATE) expression and evaluate it, you'll see it's equal to "12/30/1899", so I thought this would be as good a value to use as any. I changed the value of the ReportDate variable to "12/30/1899", and set the expression to:
@[User::ReportDate] == (DT_DATE) "12/30/1899" ? GETDATE() : @[User::ReportDate]
This seemed to work at first. However, I soon noticed that this will only evaluate properly the first time. After that, you lose the "12/30/1899" value in the variable, and your date never changes.
The Solution
The solution I used combines the above expressions, but uses a couple of string variables instead.
|
ReportDate
|
DateTime
|
Final date parameter that will be used in the queries
|
|
DateOverride
|
String
|
Parameter that can optionally be passed in on the command line
|
|
TempDate
|
String
|
Temporary variable to allow package execution in the designer
|
1. Create the variables listed in the table above
2. Set the EvaluateAsExpression = True on TempDate, and give it the following expression:
@[User::DateOverride] == "" ? "12/30/1899" : @[User::DateOverride]
3. Set EvaluateAsExpression = True on ReportDate, and give it the following expression:
@[User::DateOverride] == "" ? GETDATE() : (DT_DATE) ( @[User::TempDate] )
4. To execute the package using today's date, make sure DateOverride has no value. To use a specific date, give it a valid date string. You can do this on the command line by using the /SET command switch, like this:
/SET "\Package.Variables[User::DateOverride].Properties[Value]";"3/1/2006"
You might wonder why we need that TempDate variable. At design time, the expression engine tries to evaluate all paths of the expression, and would give you an error if you try to cast an empty string value to a DT_DATE. Adding the temporary variable that always has a valid date value gets us around this problem.
There's definitely other ways of doing this, and I'd like to hear back from anyone who has other ideas, or situations where this solution wouldn't be ideal.
