SSIS Parameter Default Values. What Are They?
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.
SELECT
parameter_name,
design_default_value,
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]
@package_name=N’LoggingMaster.dtsx’,
@execution_id=@execution_id OUTPUT,
@folder_name=N’DeployHere’,
@project_name=N’1DayOverview’,
@use32bitruntime=False,
@reference_id=Null
Select @execution_id
DECLARE @var0 sql_variant = N’c:\F4\’
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
@execution_id,
@object_type=20,
@parameter_name=N’DumpFileLocation’,
@parameter_value=@var0
DECLARE @var1 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
@execution_id,
@object_type=50,
@parameter_name=N’LOGGING_LEVEL’,
@parameter_value=@var1
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO
And our final look inside the catalog showing what was used when we executed our package
SELECT
[parameter_name]
,[parameter_value]
,[value_set]
,[runtime_override]
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.
