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.
We have a very simple SSIS package with on Data Flow Task with one OLEDB Source and one Flat File Destination connection manager.
Figure 1: Simple Data Flow Task with one OLEDB Source and one Flat File Destination
Right click on the ConnectionManager, choose “Parameterize”
Figure 2: "Parameterize" option on the connection manager.
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 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
Figure 7: Configure your SSIS Project/Package to associate parameter with the environment variable
Figure 8a: "Parameters" under "Configure..." dialog box
Figure 8b: "Connection Manager" under "Configure..." dialog box
Figure 8c: Reference Environment variable for the Parameter (s)
Figure 9: Set the value for the Environment Variable you created
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)
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