Configuration handling in SSIS was not always the easiest part. One of the problems were the different possibilities we had before SQL Server 2012:
- XML configuration files
- Environment variables
- Registry entries
- Parent package variables
- SQL Server configurations
In SSIS 2012 the configuration handling has been totally redesigned and there is now one common standard way how to do this.
This guide will show how to use the new way of configuration, how parameters and environments can be used to build dynamic SSIS packages, how they simplify the deployment process and how to migrate old configurations to the new world.
A special thanks goes to my colleague Christian Jurjut, who wrote this guide with me.
In SSIS 2012 parameters on project and package level have been introduced. In combination with the also new “environments” in the SSIS catalog, they offer a new opportunity to configure packages during runtime. This new method was introduced in order to have only a single way to apply configurations and so to eliminate the bugs and weaknesses of the old configurations.
The deployment model helps to deploy SSIS packages and projects to different environments, like a development, test and production system. Therefore it is possible to provide different configurations for each system already in the solution during design time, also to be able to test packages with different parameter settings. In order to provide those configurations it is necessary to create different scenarios in the project’s configuration manager. It is reachable under the project properties.
In the appearing window it is possible to create new configuration scenarios by selecting “<New…>” from the “Active solution configuration” drop down field.
Usually the “Server Name” and the “Server Project Path” differ between the environments. So by setting those values it is possible to deploy the SSIS packages to different systems only by choosing a value from a drop down field.
The deployment model is not completely new to SSIS projects, but with SSIS 2012 a new model was introduced to the already existing package deployment model. It is called “project deployment model” and it is mandatory to create projects using this deployment model in order to be able to use parameters and deploy SSIS packages to the SSIS catalog. The “old” package deployment model can still be used but it is not possible to apply the new configuration methods, as it does not support parameters or the SSIS catalog.
In combination with parameters the project deployment model gets more useful, as it is possible to assign different values to parameters for each scenario. So, for example, it is possible to create a scenario for each environment, like development, test and production, and to assign different connection strings to those scenarios via parameters. This gives you the opportunity to execute SSIS packages against different systems during design time only by choosing another scenario from the drop down box. But it also offers the possibility to deploy the project and packages with already the right configuration to the different environments.
Parameters are newly introduced to SSIS with SQL Server 2012. They can be created on project or package level, according to the assigned scope. If a parameter is defined on project level, it can be used in all SSIS packages within this project. If it is created on package level, the parameter can only be accessed within the according package.
Parameters can be used like variables, with a few exceptions. It is possible to assign values to them and to use them within expressions for different components. So they can be used for configuration purposes. Unfortunately it is not possible to replace variables at all by parameters, as they cannot store results from “Execute SQL Tasks”.
On project level parameters can be created by using the “Project.params” entry in the solution explorer.
By clicking the “Add Parameter” button, new parameters will be added to the list below. The following properties can be provided for them:
- The name of the parameter
- The according data type
- The default value, used during design time
- A sensitive flag
- A required flag
- And a description
If the parameter is marked as sensitive, its value will be encrypted when the project is deployed to the SSIS catalog. The required flag indicates that it is mandatory to pass a value to this parameter before the package can be executed.
To create parameters at package scope, you just have to choose the “Parameters” tab in the according package, where you can also add them by using the “Add parameter” button.
If different scenarios have been applied to the project, it is possible to assign different values to parameters for each scenario. This can be achieved by clicking the “Add Parameters to Configurations” button. The following window gives an overview of all available scenarios (columns) and the configured parameters (rows).
By clicking the “Add” button a list of all available parameters appears, which can be added to the configuration.
Integration Services Catalog
The SSIS catalog is new to SQL Server 2012 and it is used to store, manage and monitor deployed packages. The SSIS repository is now an integrated part of SQL Server and the old SSIS Service is only there for compatibility issues. This also means that we can now easily backup and restore the whole repository as a database and all the packages, configuration settings and logging information is included.
In order to be able to deploy SSIS packages to SQL Server, the integration services catalog has to be created on the server. The catalog uses CLR stored procedures, so you will need to enable CLR integration on the database engine, if it is not enabled already. After CLR integration has been enabled, you can use SQL Server Management Studio Object Explorer in order to create the catalog. As shown in the following image, right-click the Integration Services node and then click “Create Catalog”.
In the create catalog dialog box you will be asked to supply a password. This password is used to create a database master key, which will be used to encrypt sensitive data in the catalog. The master key has to be backed up separately from the database. So it is recommended to create a backup as soon as it is created. Therefore the master key has to be decrypted and an encrypted backup has to be created. The following script can be used for this purpose:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'; BACKUP MASTER KEY TO FILE = 'c:\temp\exportedmasterkey' ENCRYPTION BY PASSWORD = 'new password';
After creation the object explorer displays the catalog twice, first in the databases node and then in the Integration Services node. In the databases node, you can work with the catalog, SSISDB, like any other user database. In the integration services node, you can administer the catalog by right-clicking the corresponding child nodes.
Within the catalog projects and packages are stored in folders. On each folder it is possible to delegate user rights in order to permit administration. The catalog also stores all environments which will be used later to configure packages during runtime. So each folder, created in the SSIS catalog contains two subfolders. The “Projects” folder, where SSIS packages will be deployed and stored, and the “Environments” folder, which manages the different environments, created for certain projects.
After deploying and executing SSIS packages the integration services catalog provides information regarding versioning, execution results and monitoring. The information can be retrieved by using the provided standard reports or by querying the SSISDB. For this reason some example queries are supplied here:
- The following query selects the execution events and messages created by SSIS package execution: “Check Execution Events and Messages.sql”
- The following query provides the runtime duration for each package executed: “Check Package Execution and Duration Per Package.sql”
- The following query provides the duration for each task executed within SSIS packages: “Check Package Execution and Duration Per Task.sql”
- The last query combines the event messages with the execution duration: “Check Execution Messages and Durations.sql”
In SSIS 2012 environments in the SSIS catalog can be used to assign different configurations to deployed projects and packages during runtime. After the SSIS catalog has been created and configured and at least one custom folder has been created, in order to store projects and packages, environments can be created in the according subfolder. Each custom folder can store different projects and environments. So it is possible to reuse already defined environments in certain projects as well as to define more than one environment per project. So, for example, you can define a delta and an initial environment for your projects, if your packages can handle both.
An environment can be created by right-clicking the “Environments” folder and selecting “Create Environment” from the context menu. In the first step you can only name the new environment. Now a new entry appears in the “Environments” folder. When you open it a new window appears, which provides some general information and the ability to assign variables and permissions.
Variables have the same properties as parameters, except the “required” option. In the example above two variables were used to store the source and destination connection string and one variable, “DeltaLoad” is a used as indicator either the package is executed in delta or in initial mode.
By adding the environment as reference to a deployed project, it can be used to configure parameters and connections.
Information regarding environments can also be retrieved from the SSISDB. It is stored in the following four tables:
- internal.environments: Stores the unique id, the name and the folder in which the environment is located.
- internal.environment_variables: Provides information about variables used in environments, like the name, data type and the current value.
- internal.environment_references: Stores the relationship between projects and environments. It provides the information which project references which environment.
- internal.environment_permissions: Stores information about permissions for each environment.
These tables also offer the possibility to modify existing environments or to create new ones. So, for example, they can be used to migrate the “SSIS Configurations” table to new environments. How this can be done will be explained later on.
Package and project configuration
After a project has been deployed to the SSIS catalog, it is possible to configure it by right-clicking the project or one of the package entries and choosing “Configure…” from the context menu. The arising window manages all parameters and connection managers on project and on package scope.
If a project contains many packages and therefore many parameters, the default layout might be confusing, because it contains all available parameters and connection managers. But the current scope can easily be changed by choosing another value from the according drop down field.
For parameters the following information is provided:
- the container determines either the parameter is on project level or belongs to a package
- the name of the parameter
- its current value
For connection managers the following information is provided:
- the connection string of the connection manager
- the initial catalog and the server name as single values
- user name and password for authentication purposes
- the “RetainSameConnection” property
The value of these properties can by changed here, so that the changes take effect on the project and the corresponding packages.
Under the reference section it is possible to assign environments to the project, which can be used to configure the values of the different properties. The assignment of the environmental variables to parameters or connection manager properties can be done in the according section by choosing the “…” button next to each of these values.
In the upcoming “Set Parameter Value” window you can choose between, editing the value manually, using the default value or assigning a variable from the used environments.
For each parameter, which has to be configurable, one variable has to be created in the according environments. So, for example, if you want to dynamically configure which packages are executed in initial or delta mode, you have to create one variable per package. If you would create only one variable for the whole project, all packages would be executed either in initial or in delta mode.
Contrary to this, if more than one environment is used within a project and each of them should configure the same parameters and connection managers, it is mandatory that the variable names are equal in all environments. Otherwise the assignment cannot be performed uniquely.
Information about parameters can be also retrieved from the SSISDB table “internal.object_parameters”. It provides information about the project, to which the parameter belongs, the scope the parameter was created at, the used data type and the default value at design time.
This first chapter introduced the new configuration possibilities in SSIS 2012 and how to use them. Within the next part I will show some Best Practices which parameters you should always make configurable, how to create environment more efficiently and how to migrate existing configuration for older SSIS version to the new SSIS catalog.