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.
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.
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 I used combines the above expressions, but uses a couple of string variables instead.
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:
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.
My only comment would be that this won't necassarily work in other countries.
Tell me what date this is:
You might say 2nd January, I would say 1st February. See the problem?
There are a number of ways of representing dates that are truly unambiguous but they all basically revolve around storing dates in decreasing order of datepart
e.g. yyyy-mm-dd, yyyymmdd
There are a number of places even within the .dtsx file itself where ambiguous dates are used and interpretation could be dependant on whatever locale your O/S is set to (if memory serves - this was a bug in RTM, fixed in SP1).
The use of ambiguous dates is a pet hate of mine. But don't worry, its a common mistake made by all north americans :)
Right - but I believe the expression evaluator is smart enough to take your locale into account and resolve the date properly.
I actually use the yyyy-mm-dd format myself in the original package -- I'm Canadian after all -- and switched it to mm-dd-yyyy for this sample.
But what happens if you build the package and then pass it onto another developer that happens to have their locale set differently? Would they get the same results? I think not!
Its a wider issue than just your blog entry of course.
Your NULL(DT_DATE) is a cool idea. I am baffled why ssis doesn't allow 'set to null'. All my procs that use date ranges accept null dates. This allows me to default the range (which differs from proc to proc) or allow it to be specified.
Sometimes these procs are run from sql or an app so the logic needs to be in the proc itself, not the sql task. With your solution I would need to use a date value as a trigger in the procs rather than null.
For all the power that ssis brings, it sure has restrictions.
I found this very helpful - the only solution I could find for successfully passing a date manually into an SSIS package