SSIS Package Deployment Model in SQL Server 2012 (Part 1 of 2)

http://www.mssqltips.com/sqlservertip/2450/ssis-package-deployment-model-in-sql-server-2012-part-1-of-2/

Problem

Deployment has always been a challenge for SSIS developers to deploy packages. SSIS developers are envious of SSRS/SSAS developers as they have an easy way to create a single unit of deployment (deployment package) that contains everything needed for the deployment. The good news is the inclusion of the SSIS Package Deployment Model in SQL Server 2012. In this tip I cover what it is and how to get started to simplify your SSIS package deployments.

Solution

SSIS enhancements in SQL Server 2012 brings a brand new deployment model for SSIS project deployment. This new deployment model is called Project Deployment Model and unlike the Legacy Deployment Model where each package was a single unit of deployment, this new model creates a deployment packet containing everything (packages and parameters) needed for deployment in a single file with an ispac extension and hence streamlines the deployment process.
Apart from handling the deployment issues, managing the configuration file for each environment for each package was also a pain in the Legacy Deployment model. The new Project Deployment Model includes Project/Package Parameters, Environments, Environment variables and Environment references.
So before I jump into an example, let me first explain some of the key terms:
Project Deployment ModelAs I said before, this is the new deployment model for SSIS projects. By default all SSIS projects you create are created using this model only (you can also migrate your existing projects to this model). You can also revert back to the Legacy Deployment model if needed in Solution Explorer. To learn more about the differences between Legacy Deployment model and Project Deployment model click here.
When a project in this model is built, a deployment packet is created with an ispac extensions which includes all your packages and parameters in one packet. The deployment packet does not contain any additional files like text files, image files if you added them in the project. To learn more about Project Deployment Model click here.
Integration Service CatalogYou can create one Integration Services catalog per SQL Server instance. It stores application data (deployed projects including packages, parameters and environments) in a SQL Server database and uses SQL Server encryption to encrypt sensitive data. When you create a catalog you need to provide a password which will be used to create a database master key for encryption and therefore it's recommended that you back up this database master key after creating the catalog.
The catalog uses SQLCLR (the .NET Common Language Runtime(CLR) hosted within SQL Server), so you need to enable CLR on the SQL Server instance before creating a catalog (I have provided the step below for this). This catalog also stores multiple versions of the deployed SSIS projects and if required you can revert to any of the deployed versions. The catalog also stores details about the operations performed on the catalog like project deployment with versions, package execution, etc.... which you can monitor on the server. There is one default job provided for cleanup of operation data and can be controlled by setting catalog properties. To learn more about this click here.
Project Parameters and Package ParametersIf you are using the project deployment model, you can create project parameters or package parameters. These parameters allow you to set the properties of package components at package execution time and change the execution behavior.
The basic difference between project parameters and package parameters is the scope. A project parameter can be used in any package of the project whereas the package level parameter is specific to the package where it has been defined. The best part of these parameters is that you can mark any of them as sensitive and it will be stored in an encrypted form in the catalog.
There can be three default values for these parameters:

• Design Default value is assigned and used in BIDS (Business Intelligence Development Studio),
• Server Default value is assigned when project comes in the catalog and overwrites the Design Default value and
• Execution value is assigned in reference to a specific environment variable during execution. To learn more click here.

Environments and Environment variables
An environment (development, test or production) is a container for environment variables which are used to apply different groups of values to the properties of package components by means of environment reference during runtime.
An environment reference is the mapping between an environment variable to pass a value to a property of a package component. A project can have multiple environment references, but a single instance of package execution can only use a single environment reference. This means that when you are executing your project/package you need to specify a single environment to use for that execution instance.
When defining a variable you can mark it sensitive and hence it will be stored in an encrypted form and NULL will be returned if you query it using T-SQL. To learn more click here.

---

Example - Development Need

What I want to do in this example, is create a project with multiple packages and build a deployment packet for the deployment. Then I will deploy the project to the Integration Services catalog and create different environments (TEST and PROD) and finally I will update the deployed project properties to use an environment reference. For execution, depending on the environment selected the data should be moved to the respective environment. The design of each individual package is very simple, it first truncates the destination table, moves data from the source (always the same for this example) to the destination (which varies depending on the environment reference chosen at execution time) and finally sends a confirmation email. For example, if I am choose the TEST environment the data should move to test database or if I choose the PROD environment the data should move to the production database.
These are the steps we will cover in this tip series:

1. Create an Integration Services Catalog
2. Create a SSIS project with Project Deployment Model
3. Deploy the project to Integration Services Catalog
4. Create Environments, Environment variables (Covered in the Part 2 tip of this series)
5. Set up environment reference in the deployed project (Covered in the Part 2 tip of this series)
6. Execute deployed project/package using the environment for example either for TEST or PROD (Covered in the Part 2 tip of this series)
7. Analyze the operations performed on the Integration Services Catalog (Covered in the Part 2 tip of this series)
8. Validate the deployed project or package (Covered in the Part 2 tip of this series)
9. Redeploy the project to Integration Services Catalog (Covered in the Part 2 tip of this series)
10. Analyze deployed project versions and restored to desired one (Covered in the Part 2 tip of this series)

---

1 - Creating Integration Services Catalog...

First of all we need to create an Integration Services catalog on the SQL Server instance (note: we can create only one Integration Service catalog on the SQL Server instance, though a catalog may contain many folders and inside each folder many projects). To create an Integration Services catalog connect to a SQL Server instance in SSMS (SQL Server Management Studio) and right click on the Integration Services node under the connected server in the Object Explorer and click on Create Catalog as shown below:

By default the catalog name appears as SSISDB and cannot be changed. You need to provide a strong password for the SSISDB Integration Services catalog you are creating which is used for the database master key. Let me tell you why you need to provide this; actually the Integration Services catalog stores application data in a SQL Server database and uses SQL Server encryption to encrypt sensitive data. For encryption, it needs to have a database master key and for that only you need to provide a password here. It's recommended to backup the database master key after the Integration Services catalog creation.

The Integration Services catalog uses CLR based stored procedures and by default CLR is not enabled for a SQL Server instance, so you need to enable it before creating an Integration Services catalog.

To enable CLR on a SQL Server instance you can execute the script below. Once you have enabled CLR you can go ahead and create the Integration Service catalog as discussed above.

--Script #1 - Enabling CLR on the SQL Server Instance
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO 

Once an Integration Services Catalog gets created you can verify it in SSMS as shown below. As I mentioned before, an Integration Services Catalog stores application data in a SQL Server database and hence a database has been created with the same name as the Integration Services Catalog. If you browse through the database you will notice there are several tables which store the data, several views built on top of these tables and several stored procedure to access and manage this data.

Even though you can have a single Integration Services Catalog on each instance, each Integration Services Catalog can have several projects deployed to it. Let me first create a folder (AdventureWorks) for my project which I will be deploying next.

To create a folder, right click on the Integration Services Catalog name under Integration Services node in SSMS as shown above and click on the Create Folder menu item. This will launch the Create Folder dialog box as shown below; specify a name for the folder to create and a folder description:

Each folder that you create inside an Integration Services catalog will have two subfolders inside it by default as shown below. The Projects folder is a place where you will be deploying your SSIS project and the Environments folder is a place where you will be creating multiple environments like development, test, PPE (Pre Production environment), Production, etc... I will discuss these folders more in a later tip in this series.

---

2 - Creating a SSIS project with Project Deployment Model...

The way we develop SSIS project/package in SQL Server 2012 remains the same as what we have been doing, so I am not going to talk in detail about SSIS project/package creation (to learn basics of SSIS you can refer to this tutorial) but rather directly jump into the development for the example. One noticeable difference in the SSIS project created in SQL Server 2012 is that by default the SSIS project will be created in Project Deployment mode, but if you need to you can change it by right clicking on the project in Solution Explorer and clicking on "Convert to Legacy Deployment Model".
A SSIS project in Project Deployment model creates a deployment packet (with *.ispac extension) that contains everything (all packages/parameters) needed for deployment unlike the Legacy Deployment mode in which each SSIS package is separate unit of deployment.
As per our requirement we need to have one SSIS project with a couple of packages. I will keep the design of the package very simple, it first truncates the target table, moves the data from the source to the target table and sends the confirmation email at the end.

Now what I want is to move data to a table in a different database depending on the environment. For example, if the package is executed with the TEST environment reference then data should be moved to AdventuresWorks2008R2Test database and if the package is executed with the PROD environment reference then data should be moved to AdventuresWorks2008R2Prod database. So let me create one Project Parameter to hold the name of the database which will be passed from an environment variable and used in the TargetConnection's connection string to connect to the target database. To create a project parameter, right click on the project in the Solution Explorer and click on the Project Parameters menu item as shown below:

On the Project Parameters window, click on the New Parameter icon in the upper left and specify the name of the project parameter. In this case I want the parameter to hold the database name hence I have specified DatabaseName name for the parameter and the default value specified as "AdventureWorks2008R2Test". This means if the package is executed in the designer, data will move to AdventureWorks2008R2Test database.

The package also contains two connection managers to connect to the databases. SourceConnection connection manager connects to the source to pull data from and will remain constant whereas TargetConnection connection manager gets the database name from a project parameter and varies from environment to environment. Apart from these two connection managers to connect to the databases, I have one SMTP connection manager which is used to send confirmation email as shown in the package design.

To make a connection manager to dynamically use the database name, you need to configure the Expression property of the TargetConnection connection manager and specify the value of InitialCatalog property to come from an expression (in this case project parameter which we created above) as shown below:

As we have specified a default value for the project parameter, if you execute the package it will execute successfully and will move the data to AdventureWorks2008R2Test database (the default value specified for the project parameter).

---

3 - Deploying the project to Integration Services Catalog...

Now that we are done with project and package development, we need to deploy the project to the Integration Services catalog we created above. To deploy the project, right click on the project in the Solution Explorer and click on the Deploy menu as shown below:

Clicking on the Deploy menu item will launch the Integration Services Deployment wizard and the first screen of the wizard is a welcome screen as shown below. You can choose not to show this screen next time by clicking on the checkbox on bottom. Click on Next button to move ahead:

The second screen of the wizard is the place where you actually specify the location to deploy from; you can either choose the deployment packet (*.ispac) file or choose an already deployed package from the Integration Services catalog as the source for the deployment. Since I want to deploy from the SSIS project, I have specified the *.ispac deployment packet name:

The third screen of the wizard lets you specify the destination where you want to deploy the project. You need to choose the name of the server where you have created the Integration Services catalog and the folder in the catalog where you want to deploy the project. I will use the folder I created above to deploy the project as shown below:

As I mentioned before, a parameter can have a server default value, the next screen is the place where you define the server default values for all the parameters of the project. Here you can either use the same design default value, specify a new value or choose the value to come from a variable as shown below:

The next screen of the wizard is a review screen where you review your selections, you can click on the Deploy button to start the deployment of the project as shown below:

The final screen of the wizard shows the deployment progress and deployment status as you can see below. If there are any failures they will be marked red and you can click on the Result column to see the reason for the failure.

Since our deployed was successful as shown above, we can connect to the Integration Services Catalog using SSMS and verify the deployment as shown below:

---

Summary

In this article I talked about the basics of the new SSIS deployment model called Project Deployment Model, how it differs from the Legacy Deployment Model, how to create an Integration Service Catalog, how to create a project with the Project Deployment Model and finally how to deploy a SSIS project to the Integration Services Catalog.
Stay tuned for my next tip in this series, in which I will discuss creating environments, environment variables, setting up an environment reference in the deployed project, executing deployed project/package using the environment for example either TEST or PROD, analyzing the operations performed on the Integration Services Catalog, validating the deployed project or package, redeploying the project to Integration Services Catalog, analyzing the deployed project versions and restoring to a previous version.
Notes:

• I have shown features and power of Integration Services catalog using the UI (User Interface), but you can also manage and control it using T-SQL commands.
• The sample code, example and UI is based on SQL Server 2012 CTP 1, it might change in further CTPs or in the final/RTM release.