Post reply

Overriding a package parameter

  • December 9, 2019 at 11:34 pm

    #3706140

    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

    #3706143

    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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • December 9, 2019 at 11:50 pm

    #3706145

    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

    #3706149

    Let me try to explain better.

    1.  Under normal conditions, the Execute SQL Task will get the max(date) and assign it to a variable.
    2. But under certain conditions, we want to be able to assign a different date to that variable when we run the package manually.

    Does that make sense?  Is it possible?

  • December 10, 2019 at 12:10 am

    #3706160

    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

    #3706163

    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

    #3706173

    imani_technology wrote:

    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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • December 10, 2019 at 3:24 pm

    #3706534

    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

    #3706608

    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