We would like to parameterize SSIS 2012/2014 Package connection manager with the environment variables. We would like to make sure the connection parameter picks up the value from the Environment variables during runtime.
Step 1: Create an SSIS Package
We have a very simple SSIS package with on Data Flow Task with one OLEDB Source and one Flat File Destination connection manager.
Step 2: Parameterize connection manager of interest.
Right click on the Connection
Manager, choose “Parameterize”
Step 3: Choose ConnectionString from the Parameterize dialog box:
Figure 3: Pick up "ConnecString" from the drop down for Parameterize Property
Notice the default value that is picked is the same you created the connection string for your source. Click ok. The parameter will show up under the “Parameter” tab under SQL Server Data Tools for 2012
Figure 4: Confirm the connection information under "Parameters" tab in SQL Server Data Tools, for your package
Step 4: Execute the Data Flow Task or the package, in SSDT, make sure this
runs to the completion
Step 5: Deploy Project to SQL Server 2012, under SSISDB (at this point, you are under SSMS, connect to SQL Server, hosting the Integration Services Catalog)
Figure 5: Deploy your project to SSISDB
Step 6: Right click on Environments and choose “Create Environment….” (you are not under SSMS, connect to SQL Server, hosting the Integration Services Catalog)
Figure 6: Create Environment Variable for the parameter
Step 7: Right click on the Project (you are not under SSMS, connect to SQL Server, hosting the Integration Services Catalog)
Figure 7: Configure your SSIS Project/Package to associate parameter with the environment variable
Step 8: Configure Parameter to refer to the environment variable
Under “Parameters” tab, observe the parameter value:
Figure 8a: "Parameters" under "Configure..." dialog box
Observe the “Connection Managers” information
Figure 8b: "Connection Manager" under "Configure..." dialog box
Reference the Environment Variable under the “Configur….” Dialog
Figure 8c: Reference Environment variable for the Parameter (s)
Step 9: Set the value for the Prod variable, for your runtime value you want to point to
Figure 9: Set the value for the Environment Variable you created
Step 10: Change the parameter binding under package configuration (Right click on project or package level under SSIS DB, in SQL Server, choose "Configure")
Figure 10: Bind the parameter manually, with the environment variable.
Please note that this is the manual step that we need to perform, when we have different environment variables bound to the same parameter (i.e. one for dev, one for prod)
Step 11: Execute package by picking the Environment variable bound to the parameter
Figure 11: Execute package, dialog box 1, value is not set automatically.
Please note the red mark, this goes away, once you select the environment variable (please note that this binding has to be performed manually, as in step 10, with configuration)
Figure 12: Select "checkbox" for the environment variable to associate the parameter with the environment variable bound in step 10.
a) Note that the file will be created upon successful completion of the project
b) You can use the profiler trace against the source SQL Server to make sure it is picking up the environment variable value
c) You can look at the package execution report in 2012 to make sure it picked up the right value.
d) For safety, I erased the security related portion but here is the report looks like:
Figure 13: SSIS Runtime report for confirmation that it picked up new value and ran to success
Please note, run time value is different than the design time value for the Connection Manager