Is it possible to override a variable value calculated via an expression by passing a different value in the DTExec command line?

Question

Is it possible to override the value of a variable in a SSIS package, which is calculated via an expression, by passing in a different value on the DTExec command line?

I have a parameterized query and I'm passing variable User::StartDate into it. The package name is OpenAirExport.dtsx.

If I invoke my package using DTExec from PowerShell I can set the StartDate variable fine on the command line. For example:

& "C:\Program Files\Microsoft SQL Server\150\DTS\Binn\DTExec.exe" -File 'OpenAirExport.dtsx' `
    -Set '\Package.Variables[User::StartDate].Properties[Value];2020-09-22'

This works fine. I can see from the resulting data that the start date was indeed set to 22 Sep 2020.

99% of the time, though, the package will just be getting the previous day's data. So I set an expression on the StartDate variable to calculate its value:

DATEADD("day", DATEDIFF("day", (DT_DBTIMESTAMP)0, GETDATE()) - 1, (DT_DBTIMESTAMP)0)

(This rather complicated looking expression just counts the number of days since day 0 then adds them to day 0 and subtracts 1. This is a fairly standard way in SQL Server of stripping the time from a datetime, leaving just the date. In this case it will give a datetime of 00:00 hours yesterday)

Now if I call the package using DTExec from PowerShell without setting the StartDate variable value it gives the correct result - data from yesterday.

Every now and again a downstream process fails and we're asked to re-run a previous day's data. So I want to be able to override the calculated value for StartDate with a value passed in from the command line. But I find that if I try to set the value from the command line this value gets ignored and the expression is still used to calculate StartDate.

Is there any way I can force SSIS to override the expression value with a value passed in from the command line?

Answer 1

You are overriding it. The problem is, that en expression is evaluated every time you read/use it so the package starts up, you specify what the value should be and then when a task/component goes to use the value, the execution engine says "Oh, StartDate has an expression on it. Let's evaluate it in case the value has changed"

A easier option would be to remove the expression altogether from @StartDate. Set to the minimum (or maximum) date value which should never do anything if the package is run outside of the standard mechanism of your Posh script. With the fail safe in place, you have your standard start of

-Set '\Package.Variables[User::StartDate].Properties[Value];$RunDate'

where $RunDate was calculated beforehand.

$RunDate = Get-Date -Format "yyyy-MM-dd"
# If we need to manually adjust start date, uncomment this line and use the right date
# $RunDate = "2020-09-22"

When the downstream system fails, you explicitly change the value and patch in the assignment so it gets used.

Answer 2

I was able to do it by making the following modifications:

1. Get rid of the expressions set for variables StartDate and EndDate. Initially I left the values blank but after saving and reopening the package I found SSIS had defaulted the values to 30 Dec 1899. Very weird date (why not 31 Dec 1899, or even 1 Jan 1900 corresponding to the 0 date for the SQL Server DATETIME data type?). However, some Googling showed me others have found SSIS uses the same default date. So it wasn't something I somehow set by accident;

2. In the OLE DB Source for the SSIS package I modified the SQL Command code that received the incoming parameters. Previously it was:

   DECLARE @StartDateTime DATETIME = ?;

   DECLARE @EndDateTime DATETIME = ?;

I changed it to:

DECLARE @DefaultDateTime DATETIME = '1899-12-30';    

DECLARE @StartDateParameter DATETIME = ?;

DECLARE @StartDateTime DATETIME = @StartDateParameter;
IF COALESCE(@StartDateParameter, @DefaultDateTime) = @DefaultDateTime
BEGIN;
    -- Default to 00:00 hours yesterday.
    SET @StartDateTime = DATEADD(day, DATEDIFF(day, 0, GETDATE()) - 1, 0);
END;


DECLARE @EndDateParameter DATETIME = ?;

DECLARE @EndDateTime DATETIME = @EndDateParameter;
IF COALESCE(@EndDateParameter, @DefaultDateTime) = @DefaultDateTime
BEGIN;
    -- Default to 1 day after start date.
    SET @EndDateTime = DATEADD(day, 1, @StartDateTime);
END;

Following these modifications the package would run if I didn't explicitly set the StartDate and EndDate variables on the command line. The @StartDateTime in the SQL query would default to 00:00 hours yesterday, and the @EndDateTime in the query would default to a day later, 00:00 hours this morning.

If I did explicitly set the StartDate and/or EndDate variables on the command line the @StartDateTime and the @EndDateTime in the SQL query would be set to the StartDate and/or EndDate variable values.