Overriding a package parameter

  • 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?

  • Parameter values are assigned before the package starts executing and are read only during execution, so what you are asking is not possible.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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.

     

  • 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?

  • 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?

  • 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.

  • 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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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.

    • This reply was modified 4 years, 3 months ago by  TangoVictor.
  • 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