SSIS Deployment Strategy for SQL Server 2012

SQL Server Integration Services (SSIS) is the extraction, transformation, and loading (ETL) tool of choice for many organizations. Although the tool is great for such tasks as moving data between multiple data sources and applying transformations, there have been some problems dealing with how to deploy and configure the packages. Because SSIS packages are really just XML files, many organizations have used the "copy and configure" approach to deployments. In such cases, the packages are typically manually copied or copied using batch scripts to shared locations or msdb, where they're executed. Configuration information is then stored in configuration files or special database tables meant only for storing SSIS configuration values.

The "copy and configure" approach can lead to problems. For example, I once worked on a project where all the SSIS configuration parameters, including connection strings, were stored in a database table. As a result, whenever we restored a copy of the production database in the test environment, all the SSIS packages in the test environment would point to the production database—which isn't a good scenario.

In an attempt to solve these types of problems, SSIS 2012 provides some new package deployment features, including the new SSISDB catalog and database. SSIS 2012 stores all the packages, projects, parameters, permissions, server properties, and operational history in the SSISDB database, bringing together all the "moving parts" for any SSIS deployment. You access the SSISDB database in SQL Server Management Studio (SSMS) by expanding the Databases node in Object Explorer.

The SSISDB catalog lists the projects, folders, packages, and configuration information in a single view. It's located in a separate folder named Integration Services Catalogs, which you can access in Object Explorer in SSMS.

I'll explain how to create and set up the SSISDB catalog and database, but first it's important to know about another important change in SSIS. Starting in SQL Server 2012, SSIS is available as an Integration Services server, which is an instance of the database engine that manages package storage, execution, history, and so on. The Integration Services service is available for backward compatibility only. The recommended way is to use the Integration Services server to manage package execution.

Creating the SSISDB Catalog and Database

The first task is to create the SSISDB catalog and database. Begin by opening SSMS and navigating to the Integration Services Catalogs folder in Object Explorer. If the Integration Services Catalogs folder is empty, you need to create the SSISDB catalog and database. To do so, right-click the Integration Services Catalogs folder and select Create Catalog.

The Create Catalog dialog box will appear, showing you that the name of the database will be SSISDB. (You can't change the name.) In the dialog box, specify the password to be used by the encryption mechanism for this database and select the Enable CLR Integration check box. If desired, you can also select the Enable automatic execution of Integration Services stored procedure at SQL Server startup check box. When you click OK, you'll see that the SSISDB catalog is now available in the Integration Services Catalogs folder in Object Explorer.

The SSISDB catalog has a specific structure for organizing packages. You group individual SSIS packages into a project, and you put a group of related projects into a folder. The folder will be directly under the SSISDB catalog. Figure 1 illustrates how the entities are structured.

Figure 1: Understanding How Entities in the SSISDB Catalog Are Structured

For this demonstration, let's create a folder. Right-click SSISDB and select Create Folder. Name the folder MyTest and add a meaningful description if desired.

Creating an SSIS Package and Project

Now that you've laid the SSISDB groundwork, let's create an SSIS package and project, which you'll eventually deploy under the folder structure you just created. Here are the steps to create the SSIS project and package:

Note that SSIS 2012 lets you to create connection managers at the project level and package level. This is a departure from previous versions, where you could create connection managers at the package level only. The recommended way is to create connection managers at the project level because it ensures that all the packages in a project use the same connection strings during execution. However, in this example, the connection was created at the package level for the sake of simplicity.

Next, you need to create a Data Flow task in your package that reads from a source table and writes to a destination table. Follow these steps:

Creating the Parameters

The SSIS package you just built is ready for testing. The package's connection manager is currently pointing to your development database instance but you need to test the package against your test database instance. One way to change the connection string is to open the connection manager and change the connection string to point to the test database instance. However, altering the package to change the connection string is not only tedious but also requires redeploying the package to your test environment. This is where parameters come in handy.

SSIS lets you create parameters that can be used to specify runtime values. You can create parameters at the package level or project level in SSIS. A typical use case is when you want to use a common variable in multiple queries in a project. For example, you could create a parameter for a variable whose value is the ending date of the last quarter. When the quarter changes, you update the variable's value; all the queries designed to use this parameter will then start using the new date. Another use case is when you want to make your database connection strings configurable. In this case, you would link the connection string values to parameters, which you can change based on whether you're in the development, test, or production environment.

To try it out, let's create a parameter for the MyTest_CM connection manager in the test package. In the Connection Managers tray in SQL Server Data Tools, right-click the MyTest_CM connection manager and select Parameterize. This will bring up the Parameterize dialog box shown in Figure 4.

Figure 4: Parameterizing the Connection Manager's ConnectionString Property

Let's walk through the different options in this dialog box:

After completing the information in the Parameterize dialog box, click OK. Check the Project.params file in your solution to make sure that your parameter has been successfully created, as shown in Figure 5.

Figure 5: Checking the Project.params File to Make Sure the Parameter Was Successfully Created

The Project.params file will contain all your project-level parameters. To view the XML contents of this file, right-click it in Solution Explorer and select View Code.

At this point, you can press F5 to build and test the package. Afterward, check the OLE DB Destination table in your master database to verify that the package produced the expected results.

Deploying the Project

SSIS 2012 has two modes of package deployment: package deployment mode for deploying individual packages and project deployment mode to deploy the entire project. The default setting for packages created with SQL Server Data Tools is the project deployment mode, which is what you'll be using. You can deploy the package from SQL Server Data Tools or from the command line. (Note that SSIS 2012 doesn't support MSBuild, so you can't use it to build and manage your SSIS 2012 projects.)

Deploying from SQL Server Data Tools. In Solution Explorer, right-click your project and select Deploy to bring up the Integration Services Deployment Wizard. Click Next on the Introduction page. On the Select Destination page, enter the name of your SQL Server instance in the Server name text box. In the Path text box, enter where you want to deploy the package. In this case, you want to deploy it to the MyTest folder you created in the "Creating the SSISDB Catalog and Database" section. As Figure 6 shows, you need to include the project name (Test_Project) as part of the path. Click Next.

Figure 6: Specifying the Destination for the SSIS Project

On the Review page, review your selections and click Deploy. After the wizard completes the deployment, go back to Object Explorer in SSMS and verify that your package has been deployed to the MyTest folder under the SSISDB catalog.

Deploying from the command line. In most large organizations, deployments are done by a DBA or systems administrator. In such environments, it's a great practice to use deployment scripts as much as possible to make the deployments more manageable as the number of SSIS packages increases. In SQL Server 2012, you can deploy SSIS packages using the command-line tool ISDeploymentWizard.exe. This tool works with the .ispac file, which is created in the project's BIN directory when you build the project in SQL Server Data Tools. The .ispac file for a project typically contains the following:

If you want to see the file's contents, you can change the extension from .ispac to .zip, then open it in Windows Explorer.

To deploy the Test_Project project from the command line, you first need to copy the .ispac file to a shared folder. Then, in a Windows command shell, navigate to the folder containing the .ispac file and execute this ISDeploymentWizard command:

"%ProgramFiles%Microsoft SQL Server110DTSBinn  isdeploymentwizard.exe" /S /ST:File  /SP:Test_Project.ispac /DS:  /DP:/SSISDB/MyTest/Test_Project

(Although this command wraps here, you'd enter it all on one line in the Windows command shell.) Table 1 explains each option in the ISDeploymentWizard command and the customizations you'll need to make.

After you run the command, the project (along with the packages it contains) will be deployed to your SQL Server instance. If desired, you can put this command in a batch file and run the batch file to deploy packages to different folders under SSISDB on the same instance or on different instances.

Creating the Environment and Environment Variables

In the SSISDB catalog, each SSIS folder can have one or more server environments. An environment is nothing but a collection of environment variables, specific to the folder in which the environment is located. Most important, you can map the environment variables in an environment to the parameters in your SSIS project.

It's important to understand the difference between parameters and variables in this context. You set parameters at the package or project level when you create the package or project in SQL Server Data Tools. Variables are part of an environment in SSISDB. Parameters reside in your SQL Server Data Tools project, whereas variables reside in SSISDB.

Let's create an environment for the test project. In Object Explorer in SSMS, navigate to the MyTest folder in the SSISDB catalog and expand that folder. Right-click the Environments folder and select Create Environment, as Figure 7 shows. Name the environment MyTest.

Figure 7: Creating an Environment

Now that the environment has been created, you can create an environment variable. To do so, double-click the MyTest environment to bring up the Environment Properties dialog box. In the left pane, select the Variables page. Create a new variable named Environment_Dev that has a data type of String, as Figure 8 shows. Leave the Description field blank. In the Value field, enter the connection string for your development SQL Server instance.

Figure 8: Creating an Environment Variable

In Figure 8, notice the link to the Permissions page. Using this page, you can grant Read or Modify permissions to SQL Server logins or roles in the environment. This way, you can safeguard connection strings, passwords, and other items that you don't want people to modify in your environments. For this example, you don't need to set any permissions.

Mapping the Project Parameter to the Environment Variable

After you create an environment and the environment variables in it, you can map your project parameters to the variables. This way, when your package executes in SSISDB, it uses the variables to assign values to the package parameters. Once you have the mapping in place, changing the project's configuration (e.g., pointing all the packages in the project to a different database) requires only changing a variable's value, with no redeployment of the packages whatsoever.

Here's how to map the project parameter to the environment variable for the MyTest project:

Now every time the package is executed, it'll use the connection string from the environment variable. If you want to point your package to a different database instance, all you need to do is change the connection string in the environment variable.

A Few More Tips

I've walked you through the steps you need to perform to implement an SSISDB solution. Here are a few more tips:

If you follow these tips and implementation steps, you can deploy SSIS projects to different environments and manage their configuration using environment variables. This will not only streamline your SSIS deployments but also prevent many configuration and deployment problems.