etl
SQL Server Integration Services (SSIS) is the ETL (Extract, Transformation and Load) tool in the Microsoft Business Intelligence suite. Microsoft changed the way they handle data loads completely with the introduction of SQL Server 2005, offering complete ETL functionality as opposed to mainly Extract and Load functionality available in earlier versions. The release of SQL Server 2012 introduced another significant improvement on previous versions, providing a new way of configuring packages for deployment. Once development of packages has been completed, the challenge is to deploy those packages from a development environment to test or production environment without having to manually reconfigure them for the new environment. Configuring packages in SSIS has not always been very easy. A main problem was the different possibilities that developers had, including XML configuration files, Environment variables, Registry entries, Parent package variables and SQL Server configurations.
In SSIS 2012 the configuration management has been redesigned and now there is one common way to do this. In SSIS 2012 parameters on project and package level have been introduced, as well as environments in the SSIS catalog and together with parameters it offers a new opportunity to configure packages during runtime.
There now exists two different deployment models to help deploy SSIS packages and projects to different environments such as development, test and production. The two deployment models are the project deployment model and the older package deployment model. This article describes how to perform project deployment on SSIS packages in SQL Server 2012.
Project deployment model
Once package have been developed and tested it needs to be deployed to an environment where it will be used, this can be either development, testing or production.
In SSIS the configuration manager is used to create the different environments. To open the configuration manager either select it from the drop down list on the main toolbar next to the Start Debugging icon
Ian_Image1
or right click on the project and select Properties.
Ian_image2
To open the Configuration Manager just select Configuration Manager... in the top right corner. To create a new configuration scenario select <New...> from the Active solution configuration drop down list.
Ian_Image3
Provide a name for the configuration and either copy settings from an existing configuration or create new configuration settings. Click on Close to return to the Project Deployment model.
Ian_Image4
Usually the Server Name and Server Project Path differ between environments so by setting those values for the different environments it is possible to deploy the package to different systems only by choosing a value from the drop down list.
Now in combination with parameters the project deployment model gets more useful as it is possible to assign different values to parameters for each scenario, for example you can assign different connection strings to each of the development, test and production environments. It provides the opportunity to execute packages against different systems during design time simply by choosing a different scenario form the drop down list, and also the possibility to deploy the project and packages with the correct configurations for the different environments.
Parameters are new to SSIS with SQL Server 2012 and can be created on project or package level. If defined on project level then it can be used in all packages within that project and if it is created on package level then it can only be accessed within that specific package. Parameters can't store results from Execute SQL Tasks but other than that works similar to variables.
To create project level parameters double click on Project.params in the Solution Explorer window.
Ian_Image5
Click on the Add Parameter button to create a new parameter and set the following properties:
Name - Provide a descriptive name for the parameter.
Data Type - Specify the data type of the parameter.
Value - The default value of the parameter during design time.
Sensitive - If a parameter is flagged as sensitive then it's value will be encrypted when the project is deployed.
Required - Indicates that it is mandatory to pass a value to this parameter before the package can be executed.
Description - Provide a description of the purpose of the parameter.
Ian_Image6
Similarly to create package level parameters just click on the Parameters tab on the Package design window. New parameters are created in the same way and the same properties should be set.
Ian_Image7
If different scenarios have been applied to the project then it is possible to assign different values to a parameter for each scenario. This can be done by clicking the Add Parameters to Configurations button.
Click on Add and select the parameter you want to configure for the different scenarios.
Ian_Image8
Change the values for each scenario and click OK when finished. here you can for example set the connection string or server name or file path for the different servers.
Now that the environments have been created and the parameters have been created and configured for the different environments the integration services catalog needs to be created on the server in order to deploy packages to the server.
The catalog is used to store, manage and monitor deployed packages. The catalog uses CLR stored procedures so CLR integration needs to be enabled. After CLR is enabled you can use SQL Server Management Studio Object Explorer to create the catalog. Just right click the Integration Services Catalogs and select Create Catalog...
Ian_Image9
You will be asked to supply a password which is used to create a database master key used to encrypt sensitive data in the catalog.
Ian_Image10
After creation the catalog is displayed twice in the object explorer, first in the databases node and also in the Integration Services node. In the database node you can work with the catalog SSISDB the same as with any other user database. In the integration services node you can administer the catalog by right clicking on the child nodes.
Ian_Image11
The catalog stores all environments which will be used to configure packages during runtime. Each folder created in the SSIS catalog contains two subfolders - Projects folder where SSIS packages will be deployed and stored and the Environments folder which manages the different environments, created for specific projects.
After the SSIS catalog has been created and configured and at least one custom folder has been created to store projects and packages, then environments can be created in the appropriate subfolder. Each custom folder can store different projects and environments so it is possible to reuse already defined environments and also to define more than one environment per project.
Ian_Image12
To create a new environment simply right click the subfolder and select Create Environment... and then specify a name and description for the environment. After clicking OK a new entry appears in the Environments folder.
Ian_Image13
To configure the newly created environment just double click to open it. Three nodes exist on the left pane - General, Variables and Permissions.
The General node displays the name, identifier and description of the environment.
ian_image14b
Click on the Variables node to create variables for the environment. Specify the variable name, type, description, value and also indicate whether or not the information should be flagged as sensitive.
Ian_Image14
Information regarding environments can be retrieved from the SSISDB, stored in the following four tables in SQL Server:
internal.environments:Stores unique id, name and folder in which the environment is located.
internal.environment_variables:Provides information about variables used in environments, such as name, data type and current value.
internal.environment_references:Stores the relationship between projects and environments. It gives information on which project references which environment.
internal.environment_permissions:Stores information about permissions for each environment.
These tables also provide the possibility to modify existing environments or create new ones.
After a project has been deployed to the SSIS catalog it is possible to configure it by right clicking the project or the package entry and selecting Configure...
Ian_Image15
This opens up the window to configure the deployed SSIS package. All parameters and connection managers can be configured on both project and package scope.
Ian_image15b
For parameters the following information is provided:
Container - Parameter is either on project or package level.
Name - Parameter name.
Value - Current value of the parameter.
For connection managers the following information is provided:
Connection String - The connection string of the connection manager.
Initial Catalog and sever name- Values of server and initial catalog.
Username and Password - For authentication purposes.
RetainSameConnection - Specify if all tasks should use the same connection.
Under the References node it is possible to assign environments to the project, which can be used to configure the values of the different properties.
To assign the environment variables to parameters or connection manager properties click the ... button next to each value.
In the Set Parameter Value window choose between editing the value manually, using the default value from the package or assigning a variable from the environments.
Ian_Image16
For each parameter that has to be configurable, one variable needs to be created in the according environments.
If more than one environment is used within a project and each should configure the same parameters and connection managers then it is mandatory that the variable names are the same for all environments.
When development of the SSIS package and project has been completed we can deploy the project to the catalog that was created. When using the project deployment model, selecting deploy will immediately launch the Integration Services Deployment Wizard. In the wizard you can choose to deploy the current project or import an existing project from another server, for example you want to move your project from development to test server.
After this you select the server where you want to deploy to and also the location (folder) in the SSIS catalog.
It is important to note that you can only deploy the entire project and not individual packages within a project.