Problem
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).
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).
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).
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).
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).
Figure 5
Figure 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:
UPPER(@[$Project::RuntimeEnvironment])=="PROD"
Figure 7
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.
Figure 8
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).
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).
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).
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 will be used to populate the [RuntimeEnvironment] project parameter of the solution. We set the value of [EV_RuntimeEnvironment] in the catalog to, again, “Dev”, “Test” or “Prod” corresponding to the folder we are modifying.
Figure 12
Figure 13
Now when the solution is deployed to these environments, we can map the project parameter [RuntimeEnvironment] in the solution to the value of the environment variable [EV_RuntimeEnvironment] by opening the “Set Parameter Value” dialog with the ellipsis next to the parameter name. Here you can map parameters to environment variables, use the default from the solution, or hard-code values that will be used every time the package is executed (Figure 14).
Figure 14
Now that you have your mappings set they will be used every time the package is used from the solution. As you can see below, when the package was executed using from the DEV configuration in the catalog, the parameter [RuntimeEnvironment] was set to “Dev” when the package was run (Figure 15).
Figure 15
When the solution is validated or executed from the SSIS catalog we can now take advantage of any precedence constraints or other workflow modifications that we set up in our original solution depending on which environment we are executing in, in the same way as we did from within Visual Studio.
Even better, mapped environment variables in the catalog do not lose their mappings when a new version of the solution is redeployed - you do not have to remap them every time. This is very useful because it also means that it does not matter what your environment configuration was set to in Visual Studio when you deployed the solution, the parameters will still be controlled by the location to which you deployed – no chance for accidentally deploying a solution meant for DEV into a the PROD catalog and updating the wrong database!
Limitations
Although overall this setup is helping our team develop more effectively, there are two significant limitations that we have had to overcome.
Storing Passwords in Visual Studio
Passwords for SQL logins, etc. must still be stored in the Visual Studio solution itself during development and there is no way to retrieve the password from an external source.
Previously we used package configurations to retrieve passwords from a secure SQL database – this way we only had to update one table and all the solutions would now have the new password. This is no longer possible. The password must be saved as a project parameter, the value set to sensitive and the solution encrypted with “Save Sensitive with Password” (also note that you will not only have to encrypt the package file with the sensitive value, but also ALL other packages in the solution, AND the solution itself).
If the password is changed you will manually have to change it when you open the solution in Visual Studio or you will get a connection error.
If you have more than one developer you should avoid “Save Sensitive with User Key” because only your user will be able to use that package from then on. If you attempt to combine this with TFS source control nobody else will be able to run that package anymore.
Calling Another Package
Packages created using the project model can only call another package that is contained within the same solution. Previously we used an SFTP download wrapper package that would encapsulate a WinSCP download script. The script would be passed values for the connection target server, username, etc. and would use those to execute the download. In this way we could reuse the package for multiple SFTP connections.
This is also no longer possible. The workaround we have developed is to create an SFTP package deployed to the SSIS Catalog that contains our SFTP download code. The system stored procedure in the Integration Services Catalog database [catalog].[create_execution] can be used to execute that package from a SQL Task in SSIS. Parameters can be passed to the package using that stored procedure.
Conclusion
Visual Studio 2012+ with SSDT and the SSIS Catalog provide many useful new features that can be used to design a solid workflow harnessing the full power of both tools but in order to do so you must plan appropriately.
Hopefully the techniques outlined in this article will provide others with some direction to how such a workflow can be developed.