For all the devs out there - how do you configure the environment variables for an SSIS Package? Our preference is to create package and using parameters and variables to create two separate environments of the same package. In this post, we'll show you how.
You may encounter a scenario in which you want to run two versions of the same ETL process. You could create two copies of the same SSIS package, or you could create one package and use parameters and variables to create two separate environments of the same package. Either way works, but implementing the second method might make it easier for your team to maintain the processes, as there will only be one package to modify. As long as you keep the package parameterized and the environments created in the SSIS catalog, you will be able to run multiple versions of the same package.
For our purpose, we had to create a parallel ETL process that would run multiple stored procedures simultaneously. I decided to use this package for this example, as I wanted to create one for our staging environment (ODS layer), and one for our data warehouse.
The first thing you will want to do is parameterize your data source. In my case, I have two packages as part of the same project, both of which will use the same data source, so I’ve created a project-level connection manager, hence the package-level scope on the connection manager. However, you could do the same thing for a package-level data source.
Ensure the Property drop-down is set to “ConnectionString” and that the option “Create new parameter” is selected. Name the parameter whatever you want to call it.
If you look under the parameters tab, you will see the connection-string parameter. In my case, it is located under the Project.params tab, since I am using a project-level connection manager; if you parameterize a package-level connection manager, the parameter will appear in the Parameters tab of the particular package you are modifying.
Go ahead and deploy your project to the SSIS catalog on your desired server. You should now see your project in the folder you specified under the SSIS catalog in the Object Explorer of the target server.
Create the different environments in which you will run your packages.
Now we are going to configure our environments. Click each environment and set the settings on the Variables tab. The Name field is for the name of the connection string (I just called it “DBConnectionString”), the Type field is for the data type (should be String), you can set the Description field however you wish (mine is blank, as you can see), and the Value field is for the actual connection string (full file system path) of the particular environment. Each configuration should have a different connection string for its associated environment, but make sure to name the variables identically in each one.
After setting the variables, we need to set the configuration for the project. Click “Configure…” on the menu for the project.
Next, head to the References tab. Click “Add…” to set the type of environment reference. Each environment will be listed there twice, in the top section under Local Folder, and in the bottom under the SSISDB node. These reflect the types of environment references. I’ve included both types below, although I myself chose to use the Absolute method.
- Absolute: The bottom section. This represents the location of the environment in relation to the SSISDB root catalog. This allows you to move your project around in the catalog without invalidating its environment references. It also allows you to use these environment variables for other projects, as it allows for one central location.
- Relative: The top section. This represents the location of the environment in relation to the project’s location. This allows consistency between projects and their environments when you deploy them to different servers.
After setting the references, move back to the Parameters tab within the same window. Click on the ellipsis next to the Value field.
Make sure "Use environment variable" is selected, and select the name of the variable you created earlier, then click OK. You have now successfully configured your project for multiple environments.
Now that you have successfully created and configured your environments, you are ready to use them. You can create a SQL Agent job that will run your package in the appropriate environment, or you can run an ad hoc job from the Object Explorer. Whichever option you decide, the following pictures will help you.
After selecting either Configure (from the right-click menu in the Object Explorer) or the Configuration tab (if you’re setting up a job), you will see a notification that your connection string is empty. You can either select the ellipsis and enter a string value (full connection string) for the parameter or select the check box at the bottom to specify that you would like to use an environment variable. Once you select the environment variable, your package will be ready to run, utilizing the environment variable you just created. Click OK, and you will have successfully run your package in the correct environment.