SSIS Parameter Default Values. What Are They?

Posted on May 2, 2012. Filed under: Parameters, SSIS | Tags: , |

This blog post looks to show how to specify different value types for parameters within our SSIS packages/projects. A parameter can have a different default value at different stages of its life.

When a project is first deployed to the SSIS server then the values assigned to the parameter are the ones that are used at design time (SSDT)

You can then override those values by specifying a literal value and this will become the server default value for that parameter.

At this point you can also map a parameter value to a variable in an environment. This does not show up as a server default value.

All parameters can have their values overridden at execution time and this is known as the execution value

Let’s see what this looks like with some examples.

I have deployed a project which has 2 parameters. The values you see here are the design time values



I can also see this represented in the SSIS catalog. I am going to use the following query a few times in this article.




default_value as ServerDefault,

referenced_variable_name as VariableName

FROM catalog.object_parameters

WHERE project_id = 1

AND object_type = 20

AND parameter_name IN (‘OrderDateKey’,’DumpFileLocation’)




As we can see the parameters only have a design time value.

Now I can go in and override those values by selecting the ellipses next to the parameter and choosing the Edit Value radio button.




Again from the SSIS Catalog




We see now that the parameter DumpFileLocation has both a design time value as well as a server default.

Instead of providing a literal value I can also map the parameters to variables in an Environment. This project references two environments. I choose a variable from within the environments to map to the parameters. Note that this exercise is purely to get the variable names. It in no way ties the parameters to a specific Environment. That is done at execution time.




Once more from the Catalog




Now we have no Server default value but we show that the parameter is mapped to an Environment variable.

Now let’s execute our package but override the values at execution time


Declare @execution_id bigint

EXEC [SSISDB].[catalog].[create_execution]


@execution_id=@execution_id OUTPUT,





Select @execution_id

DECLARE @var0 sql_variant = N’c:\F4\’

EXEC [SSISDB].[catalog].[set_execution_parameter_value]





DECLARE @var1 smallint = 1

EXEC [SSISDB].[catalog].[set_execution_parameter_value]





EXEC [SSISDB].[catalog].[start_execution] @execution_id


And our final look inside the catalog showing what was used when we executed our package

  FROM [SSISDB].[catalog].[execution_parameter_values]
  WHERE execution_id = 10
  AND parameter_name IN (‘OrderDateKey’,’DumpFileLocation’)




From this query we can see that the values held in the Catalog were overwritten at execution time.

After going through this post go and have a read of this MSDN article which will help to reinforce why you need to know what are your default values. Also have a read of this article and see “fallback” in action.

Liked it here?
Why not try sites on the blogroll...


Get every new post delivered to your Inbox.

Join 201 other followers