As we have recently demonstrated on this forum, SQL Server 2012 Integration Services offers several different options for deploying and storing SSIS packages along with their associated projects (whenever applicable). These options are directly related to two deployment models available in SQL Server Data Tools console. The choice of the deployment model (Project or Package) and the resulting storage type determines, in turn, the methods that can be employed to execute packages. In this article, we will present one of these methods, which deals with packages deployed using Project Deployment Model and leverages newly introduced Environments.
In general, deploying SSIS packages and projects results in one of the following configurations. The first two of them correspond to the legacy Package Deployment Model, while the third one represents the new Project Deployment Model.
- Packages are stored in a file system outside of locations controlled by SSIS service.
- Packages are controlled by SSIS service (residing either in SSIS Package Store or MSDB database and appearing in the Stored Packages subfolder of the Integration Services node within the SQL Server Management Studio console).
- Packages are contained in projects deployed to the SSISDB catalog (appearing in the Integration Services Catalog subfolder of the SQL Server Database Engine node within the SQL Server Management Studio console).
The last of these configurations is recommended due to a number of advantages it provides. We have demonstrated those related to project and package configuration in our recent articles. Now let's focus on the benefits that can be realized when executing packages stored in this manner.
For the purpose of our presentation, we will leverage a sample package we have already used on a number of occasions. Its design is based rather loosely on the example described in SSIS Tutorial included in the MSDN Library. In our case, the package performs the export from the dbo.FactCurrencyRate table residing in the AdventureWorksDW database to a text file. This implies that our package contains two connection managers (one for the SQL Server and the other for the text file) and a single Data Flow task. The format of the output text file matches the structure of the table, with rows divided into four columns - AverageRate, CurrencyKey, Date, and EndOfDayRate.
In addition, we define two package-level parameters, representing the connection strings of the two connection managers included in the project. This can be done by invoking the Parameterize... option from the context-sensitive menu of each of them. Once the Parameterize dialog box is displayed, you need to choose the ConnectionString entry in the Property: listbox, ensure that the Use existing parameter option is enabled, and click on OK to finalize your selections. If you switch to the Parameters tab in the Designer interface, you should see both parameters listed there (named, by default, as FlatFileConnectionManager_ConnectionString and LocalhostAdventureWorksDW_ConnectionString), along with their values, data types, descriptions, as well as Sensitive and Required settings. Once you deploy the project containing the package (using Project Deployment Model), its entry should appear under the SSISDB node of Integration Services Catalog in SQL Server Management Studio.
If you have worked with earlier versions of SQL Server Integration Services (or have used package deployment model in the current one), you likely at some point have taken advantage of package configurations to assign values to properties of packages and their components when launching their execution. In the context of the project deployment model, environments provide roles equivalent to package configurations. Conceptually, they constitute collections of arbitrarily defined variables. Once defined, these variables can be subsequently mapped to parameters of packages and projects stored in the SSISDB catalog.
Let's examine how we can take advantage of SSIS environments to facilitate execution of the same package in two distinct scenarios. For the sake of simplicity, we will assume that our objective is to save an output of the data export (represented by the FlatFileConnectionManager_ConnectionString parameter of the Flat File Connection Manager in our sample package) to a designated file, whose specific location depends on whether the execution is part of UAT and Production processing.
To accomplish this, launch SQL Server Management Studio and connect to the Database Engine hosting SSISDB Catalog. Locate the project you deployed from SQL Server Data Tools under Integration Services Catalogs node. If you created a separate folder for this purpose, you should find two subfolders there, named respectively Projects and Environments. Before we proceed with configuring the latter, open the former, right-click on the icon representing the deployed project, and select the Configure... item from its context-sensitive menu. This will display Configure dialog box, with its Parameters section active and the Scope set to Entry-point packages and projects. If you were following our instructions for configuring the package, you should find three entries:
- DataFlowTask_MaximumErrorCount with the scope set at the project level and the value of 1 (of Int32 data type).
- FlatFileConnectionManager_ConnectionString with the scope set at the package level and the value containing the full path of the output text file (of String data type).
- LocalAdventureWorksDW_ConnectionString with the scope set at the package level and the value containing the connection string to the local AdventureWorksDW catalog (of String data type).
Note (by viewing Set Parameter Value dialog box) that in each of these cases, parameter values are obtained based on their content being assigned in the package (as indicated by the Use default value from package setting). While in the same dialog box you can edit the value in an arbitrary manner, the Use environment variable option is grayed out. In order to make it available, we need to first create environments (along with their variables).
Return to the Object Explorer of SQL Server Management Studio, right-click on the Environments subfolder within the folder hosting your deployed project, and select Create Environment... from the context sensitive menu. Once presented with the Create Environment dialog box, type in UAT in the Environment name text box. Repeat the same sequence of steps, but this time use Production as the label.
With both environments in place, it is time to create their respective variables. Since our intention is to control the value of a single parameter, such that it is automatically adjusted when we choose the target environment, we will name it identically in each. Display the Environment Properties dialog box (via the context sensitive menu entry of UAT environment) and switch to its Variables section. Type in FlatFileConnectionString in the Name column, ensure that the Type is set to String, and specify as its Value the full file system path where the output of our test run should be stored. Repeat these steps again with the Production environment but point to the file system location for the production output. Note that in both cases, you have the ability to enable the Sensitive attribute of each variable (this does not yield any benefits in our case, but would be worth considering if you were using the same approach to define SQL Server connection strings).
With both environments and the corresponding variables defined, let's revisit the configuration of our project. In order to take advantage of the new environments, we need to first add their references. Display the Configure dialog box of the project (by selecting the Configure... entry from its context sensitive menu), switch to its References section and click on the Add... command button to display the Browse Environments dialog box. Note that each environment is listed there twice - in the upper portion under Local Folder and in the lower under SSISDB node, reflecting the existence of two types of environment references:
- absolute - represents the absolute location of the environment (i.e. in relation to the root of the SSIDB catalog). This allows you to move the project within the SSIDB folder hierarchy without invalidating its environment references. It also facilitates configurations where multiple projects (not necessarily residing within the same folder) use a common set of environment variables.
- relative - represents the location of the environment in relation to the project's location. This facilitates maintaining consistency between projects and their respective environments in situations where you deploy them to multiple servers.
Once you added references to both environments, switch to the Parameters section in the Configure dialog box and click on the ellipsis button in the FlatFileConnectionManager_ConnectionString entry on the Parameters tab. In the Value section of the resulting Set Parameter Value dialog box, select the Use environment variable option and choose the FlatFileConnectionString entry in the corresponding listbox. After you click on the OK command button to finalize your selection, that entry (underlined) should appear in the Value column.
This completes all preparation steps for the use of environments during package execution. At this point, if you select the Execute... item in the context-sensitive menu of our sample package, you should see the notification appearing at the top of the Execute Package dialog box stating the parameter "FlatFileconnectionManager_ConnectionString" is configured to use an environment variable, but no environment has been selected. Check the "Environment" checkbox and specify the environment to use, or specify a literal value for the parameter". Once you check the Environment checkbox (at the bottom of the Parameters tab) and select either UAT or Production environment, you will be able to execute the package with the FlatFileconnectionManager_ConnectionString being set to the corresponding environment variable.
The approach described here provides one way to execute Integration Services packages stored in SSISDB catalog. In our next article, we will describe other methods of accomplishing the same goal.