SSIS Data Source Connection Information Parameterization with Environment Variable

  • Article

 

Scenario

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.

  
Figure 1: Simple Data Flow Task with one OLEDB Source and one Flat File Destination

 

Step 2: Parameterize connection manager of interest.

Right click on the Connection
Manager, choose “Parameterize”


 Figure 2: "Parameterize" option on the connection manager.

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.

 

Troubleshooting:

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

Reference:

https://msdn.microsoft.com/en-us/library/hh213230.aspx

https://blogs.msdn.com/b/mattm/archive/2011/07/22/parameterized-connection-managers.aspx

https://blogs.msdn.com/b/mattm/archive/2011/07/25/all-about-server-environments.aspx