December 9, 2019 at 11:34 pm
Here is the situation...
I want to use a new Execute SQL Task to get the max(target.lastupdated) and assign it to a variable. Then I want to assign that variable to a parameter.
But what if I want run the SSIS package from SSMS and set my own value for the parameter? What if I want to set my own date when I run the package from SSMS instead of the results of the Execute SQL Task? Is it possible to override the assignment of the variable to the parameter?
December 9, 2019 at 11:39 pm
Parameter values are assigned before the package starts executing and are read only during execution, so what you are asking is not possible.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 9, 2019 at 11:50 pm
How are you assigning to a parameter? You mean updating it in the Catalog?
We use a similar process where at the end of an ETL we update a project level parm in the SSIS Catalog that then gets used on the next execution. Its one way to persist a value from one run to the next. Is this what you mean?
That parm could be overridden by manually supplying a value, which would get used on the next execution.
If you're saying you modify the parm then use it, that won't work. Once execution begins the parms are fixed and wont change.
I'm not totally sure what you mean but one way would be to add another parm indicating that a manually supplied value is being used. Then in some expression you can check if its supposed to use the manual value.
December 9, 2019 at 11:59 pm
Let me try to explain better.
Does that make sense? Is it possible?
December 10, 2019 at 12:10 am
Here's another idea:
In the Execute SQL Task, can we check to see if the parameter has a null value? If it does, then we can SELECT the max(date). If the parameter is not null, then don't run the SELECT. Would that work?
December 10, 2019 at 12:47 am
Let me re-phrase:
1) Set the variable equal to the parameter,
2) If variable is null because the parameter is null, Then set the variable equal to the SELECT statement,
3) Else variable is equal to the parameter.
December 10, 2019 at 2:32 am
Here's another idea:
In the Execute SQL Task, can we check to see if the parameter has a null value? If it does, then we can SELECT the max(date). If the parameter is not null, then don't run the SELECT. Would that work?
Yes, you can do this using precedence constraints.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 10, 2019 at 3:24 pm
I was messing with this a bit i'm not sure you can use NULL in a parm. Seems a datetime type will default to min date after blanking out the value. Also an odd behavior i noticed is that a project parm defaults to 1/1/0001 and a package parm goes to 12/30/1899.
I'm not sure what it does when blanking it out in the project configuration, I'm seeing this behavior in VS 2017.
Either way you could still use these values in the precedence constraint.
December 10, 2019 at 6:12 pm
Thanks for pointing me in the right direction.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply