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.

• Additional articles about SQL Server Integration Services

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.

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:

1. Launch SQL Server Data Tools. In case you haven't heard, that's the tool that replaces Business Intelligence Development Studio (BIDS) in SQL Server 2012. From the File menu, select New and choose Project. Select Integration Services Project and name it Test_Project. This will create a new SSIS project that already contains a package named Package.dtsx.
2. Open the Package.dtsx package if it's not already open. Locate the Connection Managers tray at the bottom of the package designer. Right-click the Connection Managers tray and select New OLE DB Connection. In the dialog box that opens, click New to launch the Connection Manager dialog box.
3. In the Connection Manager dialog box shown in Figure 2, enter the name of your SQL Server database instance in the Server name field. Choose how the package will be connecting to your SQL Server database instance. You can use Windows Authentication or SQL Server Authentication, in which case you need to supply the SQL Server login information. In the Connect to a database section, select the master database. Click Test Connection to make sure everything is configured properly.
   

   

4. After the connection has been created, go back to the Connection Managers tray in SQL Server Data Tools. Right-click the connection you just created, select Rename, and enter the new name of MyTest_CM.

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:

1. Open the SSIS toolbox in SQL Server Data Tools by choosing SSIS, then clicking SSIS Toolbox.
2. In the SSIS Toolbox, locate the Data Flow Task icon and drag it onto the package design area. Open the Data Flow task by double-clicking the icon to get to the Data Flow view.
3. Drag the OLE DB Source icon from the SSIS Toolbox to the package design area. Right-click the OLE DB Source component and choose Edit.
4. In the OLE DB Source Editor, select MyTest_CM in the OLE DB connection manager drop-down list. In the Data access mode drop-down list, select SQL command. In the SQL command text field, enter the query

   SELECT table_name FROM information_schema.tables

   This query returns a list of all the tables in the master database. Click Preview to make sure the query works. If it does, click OK to close the OLE DB Source Editor.
5. Drag the OLE DB Destination icon from the SSIS Toolbox to the package design area. Connect the output of the OLE DB Source component to the OLE DB Destination component.
6. Right-click the OLE DB Destination component and choose Edit. In the OLE DB Destination Editor, select MyTest_CM in the OLE DB connection manager drop-down list. In the Data access mode drop-down list, select Table or view - fast load. Click the New button next to the Name of the table or the view drop-down list. The Create Table dialog box will appear. It'll contain the code to create a table named OLE DB Destination on your SQL Server instance. Click OK.
7. Select OK to close the OLE DB Source Editor and create the table in the master database. Figure 3 shows what the completed Data Flow task looks like.
   

   

8. Verify that the OLE DB Destination table has been created by running the following query in SSMS:

   SELECT * FROM [OLE DB Destination]
   

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.

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

• Property. You use this option to select the connection manager property you want to parameterize. In this case, choose ConnectionString, which means the connection string for the connection manager will be replaced with whatever value has been set in the parameter. Besides connection strings, you can parameterize other attributes, such as the server name, username, and password. Note that the attributes that can be parameterized will vary based on the SSIS component. For example, the attributes that you can parameterize for the connection manager will be different than those for a Data Flow task.
• Do not use parameter. If you select this option, the connection manager will ignore any parameters and instead use the values that have been assigned to it when the package was created.
• Use existing parameter. If you select this option, the connection manager will use the specified existing parameter.
• Create new parameter. You select this option when you want to create a new parameter. You need to specify the parameter's name and scope. Optionally, you can specify a description and value. In this case, name the parameter MyTest_CM_ConnectionString because the connection manager is named MyTest_CM. The Value field will automatically populate with the default connection string for MyTest_CM, which you can leave for now. You'll change it later. You can add the parameter to the package or project, depending on the scope you select. Basically, if you set the Scope option to Project, the parameter will be available throughout the entire project, so other packages in the project can use it as well. If you set the Scope option to Package, the parameter will be available only within the package and not at the project level. In this case, set the Scope option to Project.
• Sensitive. If you select this check box, sensitive parameter values are encrypted in the catalog and appear as a NULL value in SSMS or T-SQL query results. You'd use this option when you're parameterizing a password. In this case, leave the Sensitive check box clear.
• Required. If you select this check box, a parameter value must be specified before the package can execute. In this case, leave the Required check box clear.

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.

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.

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:

• The project's manifest
• All the packages belonging to the project
• The parameter file (Project.params)

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 Server\110\DTS\Binn
  \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.

Table 1: Options in the ISDeploymentWizard Command

Option	Description
"%ProgramFiles%\Microsoft SQL Server\110\DTS\Binn\isdeploymentwizard.exe"	Specifies the path to ISDeploymentWizard.exe. If you installed SQL Server in a nondefault location, you need to change the path accordingly.
/S	Specifies a silent installation (i.e., the UI isn't shown).
/ST:File	Indicates that the source of the project deployment package is a file. (The source can also be a SQL Server instance, in which case you'd use /ST:Server.)
/SP:Test_Project.ispac	Specifies the .ispac deployment file.
/DS:	Specifies the destination server where the project is to be deployed. You need to replace with the name of your SQL Server instance.
/DP:/SSISDB/MyTest/Test_Project	Specifies the destination path where the project is to be deployed.

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.

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.

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:

1. In Object Explorer in SSMS, right-click the MyTest project and choose Configure to bring up the Project Configuration window.
2. In the left pane, choose References. On the References page, click Add. In the Browse Environments dialog box that appears, select the MyTest environment, as shown in Figure 9. Click OK.
   

   

3. In the left pane, choose Parameters. On the Parameters page, notice that the package's MyTest_CM_ConnectionString parameter shows up. Click the ellipsis (...) next to the parameter to bring up the Set Parameter Value dialog box.
4. In the Value section of the Set Parameter Value dialog box, select the Use environment variable option and choose Environment_Dev from the drop-down list of available environment variables, as Figure 10 shows. Click OK.
   

   

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:

• Use project-level parameters for all connection strings and other configurable objects in your project.
• Deploy your SSIS project to a single folder on each SSISDB instance, then create separate environments (and variables in those environments). With this setup, you can map your project parameters to different environment variables to configure package execution.
• Secure your environments by granting Modify rights to DBAs or systems administrators only. Grant Read rights to developers.
• Make sure you map the environment variables to the project parameters. If you forget to do this, your packages will execute with whatever values were specified when the package was developed.

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.