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

image

 

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’)

 

image

 

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.

 

image

 

Again from the SSIS Catalog

 

image

 

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.

 

image

 

Once more from the Catalog

 

image

 

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’)

 

image

 

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

Follow

Get every new post delivered to your Inbox.

Join 201 other followers