SSIS 2012 Configuration Guide – Part 2: Scenarios, Setup and Migration

This post is the second part of the SSIS 2012 configuration Guide. The first part gave a general introduction about the new deployment models, parameters, environments and the SSIS catalog.

In the second part of the guide I will describe the configuration scenario in general, recommended default configurations, how to setup parameters and environments and migration options for existing SQL Server based configurations.

Configuration Scenario

As mentioned in my first post in previous versions of SSIS 2012 we had several possibilities how to configure our SSIS packages. With new concept of parameters and environments in SSIS 2012 the way to use configurations is now standardized, as shown in the picture below.

If you follow the design of master and child packages all configurable parameters are now stored in one environment, that is stored in the SSIS catalog. A SQL Server agent job, which triggers the master package of your ETL process, has an environment assigned, which will be used for package execution. All child packages can then use the parameters of the assigned environment.

This also means that the old concept of assigning different configurations also to child packages is not possible anymore. Shared configuration parameters used by several packages should be defined on a project level. If you want to share parameters across projects you need to stored them redundant in different environments and manage it on your own. I know that this is a small loose of flexibility but it is currently the only way to go.

Default Configurations

The following list contains recommended SSIS properties which should be made dynamic configurable by default in every SSIS project / package:

Connection Strings for all Connection Managers

BLOBTempStoragePath

BufferTempStoragePath

DefaultBufferMaxRows of each dataflow (optional on package level)

DefaultBufferSize of each dataflow (optional on package level)

EngineThread of each dataflow (optional on package level)

Delta or Inital Load flag (optional on package level)

Transaction Handling (optional on package level)

For a better understanding I added two screenshots to visualize the default configuration on project level…

and on package level:

As the temporary storage paths usually are the same for all packages within a project they can be created as parameters at project scope. The default buffer properties and the engine threads can differ between the packages and therefore should be created at package scope.

If packages are designed to handle both, delta and initial loads, it is recommended to configure the execution mode in order to switch between them during runtime. Therefore this table can be used to automatically create new environments. How this can be done will be explained later.

For configuration purposes all used connection managers are created on project level, so they are available for all packages. This is not mandatory, but makes it later easier to configure needed connection managers, at least when they are used in more than one package.

Deployment

Before you can use Environments you need to deploy your project to the SSIS Catalog. If you only want to test your parameters on your developer box, you can use the parameter default values that you can define in Visual Studio.

In order to deploy your project you just right-click on your project and press click “Deploy”, which will open the deployment wizard where you need to specify the SSIS server name and the target path, like in my sample below.

After you are done with the wizard you should see your project and all containing SSIS packages in the SSIS catalog.

Environment Setup

If you look carefully to your “Environments” folder you will recognize that it’s empty. In order to configure your project you need to create an environment first. Typically you will then add all defined parameters to your project environment. In larger projects this can take some time, if you do it manually. In order to make it a little bit more comfortable you can also use this stored procedure that analyzes all defined parameters of a project in the SSIS catalog (from the “internal.object_parameters” table in the SSISDB) and adds them to your new environment.

USE [SSISDB]
GO

DECLARE @RC int
DECLARE @environment nvarchar(128)
DECLARE @folder nvarchar(128)
DECLARE @project nvarchar(128)

-- TODO: Set parameter values here.

EXECUTE @RC = [dbo].[CreateEnvironmentFromParameters] 
   'MyEnvironmnent' -- name of the environment
  ,'Test' -- project folder 
  ,'Configuration_SQL2012' -- project name
GO

This and all following stored procedures can be found here:

https://skydrive.live.com/redir?resid=EDD20A747F7B8FDE!32835&authkey=!AFvRzY5jzfphR_Y

“CreateEnvironmentFromParameters_SP.sql”

The stored procedure has four input parameters, each one is mandatory:

environment: Determines the environment name for which the variables will be created. The name can be existing or new. If it is new it will be created. Otherwise the current variables will be deleted and the new configuration will be loaded.

folder_project: Determines the SSIS catalog folder name in which the project is stored. The folder has to be existent, if a non-existing name is provided, the stored procedure will raise an error.

folder_environment: Determines the SSIS catalog folder name in which the environment is stored or should be created. It can be existing or new. If it is new it will be created. Otherwise the folder will be created and the environment will be added.

project: Determines the project name, holding the parameter values which should be used for the environmental variables. The project has to be existent, as it is the reference used to create the variables. If a non-existing name is provided, the stored procedure will raise an error.

The stored procedure creates for all used connection managers and parameters within the project and all packages a variable in the defined environment. The value assigned to each variable is the design default value currently stored in the “internal.object_parameters” table. If another value should be assigned, this has to be changed manually.

Naming Conventions

As mentioned before with SSIS 2012 it is not possible to assign more than one environment during runtime. This means that all parameters, on project and on package level, for the whole ETL process that the master package triggers, need to be stored in on environment. In order to be able to differentiate between the different parameters within each package, we need to agree on a certain naming convention. I will make just a suggestion how it could look like, but feel free to take your own specific one.

So the naming convention I use has the following syntax:

General + _ + ParameterName
PackageName + _ + ParameterName

All project level parameters have the prefix “General_” and all package level parameters have the prefix “<PackageName>_”.

As an example I added a screenshot for my suggested default parameters.

Map environment variables to project parameters

After the environments and variables have been created, they have to be mapped to the according project and parameters. This can be done manually by defining the references between projects and environments in the “Configuration” window and by mapping the environmental variables to each parameter and connection string in the project and its’ packages.

As this can get very time consuming, especially for bigger projects containing many packages and parameters, it is recommended to use the following stored procedure in order to create the references and variable mappings.

“CreateVariableParameterMapping_SP.sql”

(Important: The stored procedure can only be used, if the creation of the environment and its variables has been done by the stored procedure “CreateEnvironmentFromParameter_SP.sql”)

The stored procedure has four input parameters, each one is mandatory:

environment: Determines the environment which holds the variables to be mapped. The environment has to be existent, if a non-existing name is provided, the stored procedure will raise an error.

folder_project: Determines the SSIS catalog folder name in which the project is stored. The folder has to be existent, if a non-existing name is provided, the stored procedure will raise an error.

folder_environment: Determines the SSIS catalog folder name in which the environment is stored. The folder has to be existent, if a non-existing name is provided, the stored procedure will raise an error.

project: Determines the project name, holding the parameter values, which should be mapped. The project has to be existent, if a non-existing name is provided, the stored procedure will raise an error.

The stored procedure checks each of the parameters for existence and raises an error, if only one is missing. It also checks, if between the declared project and environment already a reference is defined. If not, it will be created automatically by the stored procedure. Afterwards the mapping between environmental variables and project parameters will be performed.

Migration of old SSIS configurations

In order to migrate configurations from projects using prior versions of SSIS 2012, the following stored procedure can be used to migrate existing SQL Server configurations. If you use XML configuration files you can transform them to SQL Server configuration and then apply the script:

“CreateEnvironmentFromConfiguration_SP.sql”

The stored procedure has four input parameters:

environment: Determines the environment to which the configuration values should by migrated. If the specified environment does not exist, it will be created.

folder: Determines the SSIS catalog folder name in which the environment is stored. If the specified folder does not exist, it will be created.

database: Determines the database storing the configuration values of the prior SQL Server version. The database has to be existent, otherwise an error will be raised.

configuration filter: This parameter is optional. If it is not specified, the whole configuration table will be loaded into the specified environment. If a value is provided for this parameter, only the corresponding values will be added to the environment.

The stored procedure also offers the possibility to migrate certain configurations to one environment. Therefore it has to be executed several times with different configuration filter settings. So all defined configuration filters will be loaded into the same environment.

If a non-existing configuration filter is provided, only an empty environment will be created.

Conclusion

SSIS package configurations deliver a common way to implement ETL packages in a highly dynamic manner and to reduce direct adaptions after implementation has been finished and the packages have been rolled out to an environment. In combination with the new deployment model, the SSIS catalog and the also newly introduced environments configuration management becomes more comfortable and standardized.

Normally the creation and setup of the new configuration method would take a lot of time, at least for bigger projects, but with the delivered guidelines and stored procedures you can easily create and migrate new environments, variables and parameter references. Therefore it is recommended to migrate prior versions to the new deployment and configuration model.

If you have further questions, recommendations or tips please let me know.

About these ads