Use an Environment Variable to configure the ConnectionString in a SSIS Project in SSISDB

• Gert.Broekhaar

  SSC Enthusiast

  Points: 179

  More actions

  August 4, 2017 at 8:47 am

  #402395

  Working on my first SSIS 2014-project, should I conclude that during configuration of a project in SSISDB, Connection Managers can only be set through parameters? In all the examples on the internet I see people first Parametrize the connection managers in Visual Studio using a Project Parameter.
  In a SSIS 2014 project I deployed to SSISDB I was directly setting the ConnectionString-property on the Connection Managers-tab with an Environment Variable (The dialog Set Parameter Value allowed me to do that!) but when running the package (simple Execute (package within project) from SSISDB) this doesn't seem to work. In the execution report I see that (my old) design time values for the separate connection elements (InitialCatalog etc.) are used.

  Using the Environment Variable for the ConnectionString-property did not seem to override these values?? How should this work??

• Phil Parkin

  SSC Guru

  Points: 247159

  More actions

  August 4, 2017 at 1:37 pm

  #1954199

  Gert.Broekhaar - Friday, August 4, 2017 8:47 AM

  Working on my first SSIS 2014-project, should I conclude that during configuration of a project in SSISDB, Connection Managers can only be set through parameters? In all the examples on the internet I see people first Parametrize the connection managers in Visual Studio using a Project Parameter.
  In a SSIS 2014 project I deployed to SSISDB I was directly setting the ConnectionString-property on the Connection Managers-tab with an Environment Variable (The dialog Set Parameter Value allowed me to do that!) but when running the package (simple Execute (package within project) from SSISDB) this doesn't seem to work. In the execution report I see that (my old) design time values for the separate connection elements (InitialCatalog etc.) are used.

  Using the Environment Variable for the ConnectionString-property did not seem to override these values?? How should this work??

  Connection Managers do not need to be parametrized in VS – they appear by default in the Connection Managers tab when configuring a deployed SSIS project.
  When you execute a deployed package directly from SSMS, you should see this:
  
  Note how it is necessary to check the 'Environment' box and select the environment to be used. Are you doing this?

  ---