SSIS and SSDT 2012+ have many useful features: easier configuration of variables for test and development environments, more detailed reporting, simpler organization and better integration with TFS for source control. As part of an upgrade to SQL Server 2014 we migrated our SSIS packages into the SSIS catalog and brought them into a source-controlled environment. It seems a lot of users have had difficulties with implementation of such an environment since there are a lot of nuances that need to be taken into account. In this article, I will outline some of the difficulties we came across and how we implemented a successful methodology and architecture.
Some of the questions we had to answer were:
- How can we set up Visual Studio solutions so that they can be deployed to the different environments in a catalog and still retain separate target locations for development, testing and production? How can these separations be maintained in the SSIS Catalog as well?
- How do we set up a Visual Studio solution so that we can reference different file paths during development and not have the package interfere with production data?
- How do we avoid having multiple SSIS package files, one for development, one for production runs, one that’s a backup of a previous version, etc. in the same solution? This is especially problematic with deployments to the SSIS Catalog because a single package cannot be deployed on its own – only the entire solution.
- How do we allow multiple developers to modify the same solution without getting in each other’s way?
- How do we implement easy configuration of passwords and usernames for connections?
The fundamental key to developing our environment was to allow different configurations in Visual Studio to modify project parameters during development, and then to use environment variables in the SSIS catalog to pass values to those parameters during runtime. This allows us the flexibility we need.
Useful Features of Visual Studio
Visual Studio with SSDT 2012+ has a number of features that has improved development and deployment capabilities that we use to resolve these issues:
Parameters. Project model solutions use parameters which can be used to specify targets at runtime. Similar to variables, these parameters are populated when execution begins but cannot be modified thereafter. Parameters can be either project-level or package-level depending on their scope. Project-level parameters are edited by opening the Project.params tab from the solution (Figure 1).
Parameterized connection managers. Parameters can be used to modify connection manager properties at runtime. The parameters are specified from the “Parameterize..” option in the connection manager context menu (Figure 2).
The “Property” drop-down can be used to select different properties, and new or existing parameters can be specified as the source for each property. We use the ServerName, UserName, Password and Initial Catalog properties to allow us to specify different servers or databases on those servers during runtime. The whole connection string can be used as well but it doesn’t lend itself to easy modification (Figure 3).
Configuration Manager. Visual Studio’s configuration manager can be used to specify which environment the execution will run against. This is not new to Visual Studio, but can be used much more effectively now in concert with other, new, features.
The configuration manager can be used to modify options and parameters.We create three configurations – one each for Dev, Test and Prod environments (Figure 4).
Configuration-based parameter values. Different parameter values can be assigned to at runtime based on the selected configuration. We can therefore modify the server name, for example, for a specific configuration manager depending on whether the server is production or development. This is done using the “Add Parameters to Configuration” option in the Project Parameters window (Figures 5 and 6).
Precedence constraint expressions. These can be modified to allow flow based on the result of an expression. The data flow path can be altered in a package based on a parameter value.
Source control integration. Visual Studio 2012+ has solid integration with TFS source control (Git is also available as an alternative). With multiple developers, source control for any development environment, including SSIS, should be implemented and this is now much easier in Visual Studio.
Developing in Visual Studio
Our Visual Studio projects are set up with three different configurations – DEV, TEST and PROD. We set up other project parameters to have their values populated based on the selected configuration.
In each new solution, we create a parameter [RuntimeEnvironment]. The purpose of this parameter is to provide a way for the package to check what environment it is running in. [RuntimeEnvironment] will have the same value as the name of the configuration – i.e. “Dev” when the DEV configuration is selected, “Prod” when PROD is selected, etc.
This parameter can be used to modify package data flows depending on which environment is selected by modifying the associated precedence constraints. For example, in Figure 7, the precedence constraint between the Data Flow Task and the Send Mail Task is set to “Expression and Constraint” with the expression set to:
This prevents execution of the email task unless the configuration is set to PROD. This way, for example, if you have an email alert that is sent out to stakeholders during production runs, you can ensure it does not get sent out when you are simply developing and the configuration is set to DEV.
Specifying the Target Server and Database
In a similar way, we can use parameterized connections to specify different server and database names during runtime depending on the configuration environment that is selected. In Figure 8 we see how the value of the [TargetDatabase] parameter changes from “tempDB” to “SSISWorkflow” when we select the “Prod” configuration environment from the drop-down.
Now if we parameterize the connection manager “local” in the solution, we can specify that the value for the [InitialCatalog] property of the connection will be derived from the project parameter [TargetDatabase] by selecting “$Project:TargetDatabase” from the “Use Existing Parameter” drop-down in the parameterization properties dialog (Figure 9).
To check that it worked the way we expected, we can open the “local” connection manager properties when the configuration is set to Dev, and compare it to when it is set to Prod – the target database has changed (Figure 10).
Extending Configurations to Filesystem Operations
In a similar way we can easily change the location of files on the filesystem that are being accessed by a solution so that files used for development and testing do not have to reside in the same location as those used in production.
For this we create a project parameter, [SSISFilestore_Root], in the solution. On our server, we create a share with a subfolder, \SSISFilestore\, to house all our files. Within that folder we create separate subfolders for DEV, TEST and PROD and, within each of those, the files for each solution are separated into their own respective folders. For example, the SQLServerCentral solution would have all its files for development under the folder \\MyServerName\SSISFilestore\DEV\SQLServerCentral\, but the production files would be stored under \\MyServerName\SSISFilestore\PROD\SQLServerCentral\.
Returning to the solution, we convert any absolute filesystem references to relative references by using expressions that reference the [SSISFilestore_Root] project parameter. For example, a reference to a target text file for outputting a dataset we would use an expression to modify the ConnectionString (Figure 11).
The expression is now set to: @[$Project::SSISFilestore_Root]+"\\Files\\test.txt" (note the escaped backslashes). When the configuration environment is set to PROD, the connection string will be “C:\SQLServerCentral\Prod\Files\test.txt”.
The SSIS Catalog
The catalog has some additional features that are useful in our environment setup:
- Folders and Environment Variables. The catalog can be used to separate different copies of the same packages in different folders, with each folder providing a different set of Environment Variables that can be configured to provide values to project parameters.
- Quick rollbacks. Solutions deployed to the catalog will be stored as separate versions and an emergency rollback to a previous version is very quick and simple to perform if necessary.
Deploying to the SSIS Catalog
These features of SSIS also assist us when deploying the package to the SSIS catalog. We can use the catalog environment variables to similarly control the value of the runtime parameters during execution and therefore ensure the same configuration changes we set up in the solution also get executed in the same way after deployment.
Using Environment Variables to Specify Runtime Parameters
In our catalog, we set up three folders – “DEV”, “TEST” and “PROD”. In each folder we set up variables that correspond to the parameters in the solution (Figure 12). In our case we chose to add the prefix “EV” to each environment variable to give an easier visual indication of the source of the values (Figure 13).
For example, we create an environment variable called [EV_RuntimeEnvironment] that wil