SSIS Environments and Parameter Values

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

After Reading this article I wanted to have a play with using Environments for package execution but also I wanted to mix up things to see how they played out.
This blog post is not about Environments per se.  If you want a good read on those then I would recommend this post by Jamie Thomson
This article is about using Environments for package execution where sometimes the Environment does not have all the information required to configure the package/project.  It is also about understanding what values get used at runtime when we execute our package.  Again here I would recommend re-reading the MSDN article and make sure you understand the precedence with which values are used in a package’s execution.

Let’s set the scene

I have a Project deployed to the SSIS Server and this project has many packages
The project itself has 2 parameters DumpFileLocation and OrderDateKey which are used in a package called LoggingMaster.dtsx to configure where the output files will be dumped (DumpFileLocation) and also for what date we want to extract (OrderDateKey).
image

Project Parameters

The values you can see for the parameters are the Design Time values (the ones used in SSDT).
I have as part of the folder to which this project is deployed two Environments E1 and E2
Each Environment has 2 variables which will map to the 2 project parameters seen earlier.  Note the names of the variables in each Environment, they are not the same.

image
Environment E1

image
Environment E2

In this blog example I am going to map the variables in Environment E1 to the Project parameters.  This is where I encounter the first thing that will make things confusing.  The project is allowed to use both the Environments.  The Environments do not match in all their variable names as we have seen.  FileLocation is available in both Environments but the other variable is different in each Environment.  When it comes to mapping the value for the parameter OrderDateKey I am presented with a choice of variables (which match on data type).  I know I want DateKey here as that is the variable in E1

image

NOTE: Variables must come from the same environment.
image
Mapped Variables to Parameters

NOTE: Mapping variables to parameters in no way restricts the environments you can use at execution time.  The exercise of mapping is just for the parameter to variable metadata in the SSIS catalog.

We can look at our variable to parameter mappings another way by querying the SSIS Catalog

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

This shows that we have only specified a Design value, no Server value and that the parameters are also mapped to variables.  From our earlier article we can assume then that should no environment variable be available it will fail back to the design time value as no server value has been specified (ServerDefault  = null)

The Execution

I will be executing my SSIS package in TSQL by using the stored procedures so I need to know the relationship between our Environments and our project.  I need to know the reference_ID of the relationship and to do that I need to query catalog.environmnet_references

SELECT reference_id, environment_name
FROM catalog.environment_references
WHERE project_id = 1

image

The first run through will use the reference E1 just to show what happens.  The files should land in the folder c:\F2\ and the date used for the extract should be 20070801

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=1
Select @execution_id
DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
@execution_id,
@object_type=50,
@parameter_name=N’LOGGING_LEVEL’,
@parameter_value=@var0
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

Here are the files on the file system and you will also note I incorporate the datekey parameter into the names of the files

image

Let’s look in the catalog too to make sure we know what values were used for the parameters.

SELECT parameter_name, parameter_value
FROM catalog.execution_parameter_values
WHERE execution_id = 2
AND parameter_name IN (‘OrderDateKey’,’DumpFileLocation’)
image

Now let’s switch the Environment reference.  You could simply go back into SSMS and right click on the package and execute from there.  In the UI dialogs that follow you could specify that the package uses E2 and try to remap the parameters to the variables in E2 but you are not allowed to do this.  For the OrderDateKey parameter you need to specify a literal value.  Even though we have changed the Environment to be used in the bottom pane of the dialog, when we click on the ellipses next to OrderDateKey there is no option for us to choose a variable from the Environment
image

Instead of doing that what I am going to do is simply use the same piece of TSQL I used before but change the value for @reference_id

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=2
Select @execution_id
DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
@execution_id,
@object_type=50,
@parameter_name=N’LOGGING_LEVEL’,
@parameter_value=@var0
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

Now remember that this environment has no DateKey variable name.  At execution time what will happen?

1)    Because E2 has no DateKey variable the execution will use design time values for both parameters (ignore the Environment altogether)
2)    E2.FileLocation is used and the design time value for the OrderDateKey parameter

image

We can see that the DumpFileLocation parameter has a value but not the OrderDateKey.  So what value is used for the OrderDateKey?

image

From the output of the package execution we see that the files were deposited in the F3 directory which is consistent with the E2 environment values.  The filenames have in them the DateKey of 20050701 which as we saw earlier is the design time value for this parameter.
This is useful to know because it means that variable to parameter mapping is not all or nothing.  If some variables can be mapped then they will with the others adopting their design time values.
Let’s query the Catalog to see how we can know which values were used.

SELECT
epv.parameter_name,
CASE WHEN epv.parameter_value IS NULL AND op.value_set = 1
THEN op.design_default_value
ELSE  epv.parameter_value END as ParameterValueUsed
FROM
catalog.execution_parameter_values epv
JOIN
catalog.object_parameters op
ON
epv.parameter_name = op.parameter_name
WHERE
epv.execution_id = 3
AND
epv.parameter_name IN (‘OrderDateKey’,’DumpFileLocation’)
AND
op.object_type = 20

image

When I allow multiple Environments in a project I do ensure that variables which are going to be used for the same parameters are named the same.  Saves a lot of head scratching later.
As a side note here is something you could do instead of blindly executing the package.  We can validate the package against an environment using catalog.validate_package.  This will tell us if we are missing anything.
Let’s do that now

declare @vid bigint
exec catalog.validate_package
‘DeployHere’,
’1DayOverview’,
‘LoggingMaster.dtsx’,
@vid out,
0,
‘S’,
2
select @vid

image

Why is this Important

It is important to understand which values will be used for parameters in the execution of a package.  Failing back to server defaults or even design time values may yield results that are unexpected and unwanted.  Imagine if the parameter that could not be mapped was the connection string of the source server and that this happens in Production.  If the design time value was for our development environment then this could result in us loading the Production destination with Development source data.

One Response to “SSIS Environments and Parameter Values”

RSS Feed for Copper Blue Consulting Comments RSS Feed

[...] 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. Share [...]


Comments are closed.

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

Follow

Get every new post delivered to your Inbox.

Join 201 other followers