Day number 9 of the 31 Days of SSIS.  If you missed the first week then start by reading the introductory post.  Since it is Sunday, I’m going to go with something a little lighter and shorter – or at least that’s the expectation as this starts.

In a previous post, I discussed variables and how to use one variable to control multiple variables.  Today, the discussion will be similar in nature.  The idea is to demonstrate how control the behavior of variables defined by expressions at run-time.  This may sound all convoluted but the rest will straighten out this confusion.

Variable Expressions

As was previously mentioned, the values for variables with expressions are calculated at run-time.  The result of which is any value that a variable has prior to the use of the variable in the package is discarded when the variable is called.  The value is calculated and returned when the variable is referenced.

The unintended effect of this is often encountered when you need to change the values in an SSIS package for a one-off execution.  If you pass in some custom values through configuration settings or command line options, the values end up being discarded when the expression fires on those variables.

Let’s build at an example SSIS package to demonstrated.  This package will be required to return data for the previous month based on values calculated at run-time.

First, create a new package and add two variables to the package with the following expressions on them:

• StartDate: DATEADD(“mm”,  DATEDIFF(“mm”,  (DT_DBTIMESTAMP) “1900/01/01”, GETDATE())-1,  (DT_DBTIMESTAMP) “1900/01/01”)
• EndDate: DATEADD(“ms”, -3, DATEADD(“mm”,  DATEDIFF(“mm”,  (DT_DBTIMESTAMP) “1900/01/01”, GETDATE()),  (DT_DBTIMESTAMP) “1900/01/01”))

The Variables window will look like the following:

Then add a Script Task to the Control Flow.  Add two variable in as ReadOnlyVariables.  The following code should be added to the Script Task code to replace “public void Main()”:

public void Main()
{
MessageBox.Show("StartDate: " + Dts.Variables["StartDate"].Value.ToString() + Environment.NewLine
+ "EndDate: " + Dts.Variables["EndDate"].Value.ToString());

Dts.TaskResult = (int)ScriptResults.Success;
}

Now run the package to see the results.  You should get a message box in the execution that looks like this:

For normal execution, this is perfect.

But let’s pretend for a moment that there are end users.  Further, let’s pretend that they end users somehow managed to put bad data into the database that this package was retrieving data from.  These end users go out and fix their data.  And now they want you to re-run the package for November 1 to November 30.

Go back to the package and change the values for StartDate and EndDate to November 1 and November 30, respectively.  Run the package and what results do you see?

I bet they look a lot like this:

Exactly not what they wanted and hopefully what you expected.  The variable are calculated at run-time regardless of the values that appear in the SSIS package when the package starts.

Override Expressions

Getting around this issue is actually very easy.  To get started, add two more variables to the package.  The variables should be the following with the listed initial values:

• OverrideStartDate: 1900/01/01
• OverrideEndDate: 1900/01/01

Change the expressions on the other two variables to the following:

• StartDate: @[User::OverrideStartDate] == (DT_DBTIMESTAMP)”1900/01/01″ ? DATEADD(“mm”,  DATEDIFF(“mm”,  (DT_DBTIMESTAMP) “1900/01/01”, GETDATE())-1,  (DT_DBTIMESTAMP) “1900/01/01”) : DATEADD(“d”,  DATEDIFF(“d”,  (DT_DBTIMESTAMP) “1900/01/01”, @[User::OverrideStartDate]),  (DT_DBTIMESTAMP) “1900/01/01”)
• EndDate: @[User::OrverrideEndDate]  == (DT_DBTIMESTAMP)”1900/01/01″ ? DATEADD(“ms”, -3, DATEADD(“mm”,  DATEDIFF(“mm”,  (DT_DBTIMESTAMP)”1900/01/01″, GETDATE()),  (DT_DBTIMESTAMP)”1900/01/01″)) : DATEADD(“ms”, -3, DATEADD(“d”,  1 + DATEDIFF(“d”, (DT_DBTIMESTAMP) “1900/01/01”, @[User::OrverrideEndDate]),  (DT_DBTIMESTAMP) “1900/01/01”))

The Variables window will look like the following:

If you execute the SSIS package with the two new variables with the values listed above you’ll see results similar to those the first time this was executed:

But the goal here is to run the package with a different time frame.  To test this change the variable to the following values:

• OverrideStartDate: 2010/11/01
• OverrideEndDate: 2010/11/15

Now executed the package and you’ll see that the package can run for another time frame.

With a little extra work on variable expressions, packages can be made to more dynamic.  These extra configuration point helps provide an insurance policy when end users or clients want a little bit more out of the SSIS package.

And let’s face it, they probably already expect the process will be able to do this without having requested the functionality.

Another Consideration

Without going into details, there is another thing to consider when writing expressions on variables.   That is the use of the GetDate() function.

First, remember that variables are calculated whenever the variable is called.  Next packages take time to execute.  Thus the value of GetDate() will change as the package executes.

After looking at these, the value of any expression that uses GetDate() will change throughout the execution of an SSIS package.  This means you have to be careful when you use GetDate() in variable expressions.

As an alternative, consider using @[System::StartTime] instead of GetDate().  It returned the date and time that a package starts and is static throughout the execution.

Wrap-Up

This post wasn’t as short as I expected and I managed to combine another post in leaving room for another in the schedule.  If you have any questions regarding variable expressions, feel free to ask.

Download

09of31-OverrideVariable