I have read several articles about Environment variables but I can’t find how to apply their usage in my case. I am developing SSIS packages on my local machine. Once they are finished I plan to deploy them on staging an production server. My SSIS project consists of several packages which most of them connect to 2 databases (but each server has it’s own copy of db) and few excel files.
So, I want to deploy my packages to 3 different servers. Based on server, connection strings would be different. Since this is still development phase I would have to redeploy most packages from time to time. What would be the best practice to achieve this?
Creating your folder
In the Integration Services Catalog, under SSISDB, right click and create a folder giving it a name but do not click OK. Instead, click Script, New Query Editor Window. This gives a query like
DECLARE @folder_id bigint EXEC [SSISDB].[catalog].[create_folder] @folder_name = N'MyNewFolder' , @folder_id = @folder_id OUTPUT SELECT @folder_id EXEC [SSISDB].[catalog].[set_folder_description] @folder_name = N'MyNewFolder' , @folder_description = N''
Run that but then Save it so you can create the same folder on Server 2 and Server 3. This will be a theme, by the way
Creating your environment
Refresh the dropdown under the SSISDB and find your newly created folder. Expand it and under Environments, right click and Create New Environment. Give it a name and description but DO NOT CLICK OK. Instead, click Script, New Query Editor Window.
We now have this code
EXEC [SSISDB].[catalog].[create_environment] @environment_name = N'DatabaseConnections' , @environment_description = N'' , @folder_name = N'MyNewFolder'
Run that and save it for deployment to Server 2 and 3.
Adding values to an Environment
Refresh the Environments tree and under the Properties window for the newly created Environment, click to the Variables tab and Add your entries for your Connection strings or whatever. This is where you really, really do not want to click OK. Instead, click Script, New Query Editor Window.
DECLARE @var sql_variant = N'ITooAmAConnectionString' EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name = N'CRMDB' , @sensitive = False , @description = N'' , @environment_name = N'DatabaseConnections' , @folder_name = N'MyNewFolder' , @value = @var , @data_type = N'String' GO DECLARE @var sql_variant = N'IAmAConnectionString' EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name = N'SalesDB' , @sensitive = False , @description = N'' , @environment_name = N'DatabaseConnections' , @folder_name = N'MyNewFolder' , @value = @var , @data_type = N'String' GO
Run that query and then save it. Now when you go to deploy to environment 2 and 3, you’ll simply change the value of
To this point, we have simply positioned ourselves for success in having a consistent set of Folder, Environment and Variable(s) for our packages. Now we need to actually use them against a set of packages. This will assume the your packages have been deployed to the folder between the above step and now.
Right click on the package/project to be configured. You most likely want the Project.
- Click on the References tab. Add… and use DatabaseConnections, or whatever you’ve called yours
- Click back to Parameters. Click to Connection Managers tab. Find a Connection Manager and in the Connection String, click the Ellipses and change it to “Use Environment Variable” and find your value
- DO NOT CLICK OK! Script -> New Query Editor Window
At this point, you’ll have a script that adds a reference to environment variable (so you can use it) and then overlays the stored package value with the one from the Environment.
DECLARE @reference_id bigint EXEC [SSISDB].[catalog].[create_environment_reference] @environment_name = N'DatabaseConnections' , @reference_id = @reference_id OUTPUT , @project_name = N'HandlingPasswords' , @folder_name = N'MyNewFolder' , @reference_type = R SELECT @reference_id GO EXEC [SSISDB].[catalog].[set_object_parameter_value] @object_type = 30 , @parameter_name = N'CM.tempdb.ConnectionString' , @object_name = N'ClassicApproach.dtsx' , @folder_name = N'MyNewFolder' , @project_name = N'HandlingPasswords' , @value_type = R , @parameter_value = N'SalesDB' GO
This script should be saved and used for Server 2 & 3.
All of that makes is so you will have the configurations available to you. When you schedule the package execution from a job, you will end up with a job step like the following
EXEC msdb.dbo.sp_add_jobstep @job_name = N'Demo job' , @step_name = N'SSIS job step' , @subsystem = N'SSIS' , @command = N'/ISSERVER "\"\SSISDB\MyNewFolder\HandlingPasswords\ClassicApproach.dtsx\"" /SERVER "\".\dev2014\"" /ENVREFERENCE 1 /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E'
- The Command is obviously the important piece.
- We are running the package
- Run this on the current server with an instance of Dev2014
- Use Environment reference 1
- We use the standard logging level.
- This is a Synchronous call meaning that the Agent will wait until the package completes before going to the next step
You’ll notice all of the above was nice and specified text strings instead of random integer values, except for our Environment Reference. That’s because you can have the same textual name for an environment in multiple folders. Similar to how you could deploy the same project to multiple folders but for whatever reason, the SSIS devs chose to provide fully qualified paths to a package while we use “random” integer values. To determine your environment ID, you can either run the following query
SELECT ER.reference_id AS ReferenceId , E.name AS EnvironmentName , F.name AS FolderName , P.name AS ProjectName FROM SSISDB.catalog.environments AS E INNER JOIN SSISDB.catalog.folders AS F ON F.folder_id = E.folder_id INNER JOIN SSISDB.catalog.projects AS P ON P.folder_id = F.folder_id INNER JOIN SSISDB.catalog.environment_references AS ER ON ER.project_id = P.project_id ORDER BY ER.reference_id;
Or explore the Integration Services Catalog under Folder/Environments and double click the desired Environment. In the resulting Environment Properties window, the Name and Identifier will be greyed out and it is the
Identifier property value that you need to use in your SQL Agent’s job step command for the
If you’re careful and save every thing the wizard does for you, you have only 1 thing that must be changed when migrate changes throughout your environment. This will lead to clean, smooth, repeatable migration processes and you wondering why you’d ever want to go back to XML files or any other configuration approach.
If you are looking for a no-code solution for this, you can try SSIS Catalog Migration Wizard from Visual Studio Marketplace.
You can migrate SSIS projects, environments, environment references, default values of project and package parameters, etc. from selected catalog folders to other servers or even to Azure SSIS runtime.