How to link ENVIRONMENTS_VARIABLES TABLE WITH projects table --SSISDB

• latitiacasta

  SSCommitted

  Points: 1982

  More actions

  June 6, 2017 at 6:38 am

  #326409

  I want information of different connection string using by each project I use below query. But it give me repetition of value for each project.

  SELECT DISTINCT p.name AS ProjectName,e.name AS EnvironmentName,

  ev.value AS ConnectionString FROM catalog.projects p

  JOIN catalog.environment_references er ON p.project_id=er.project_id

  JOIN catalog.environments e ON er.environment_name=e.name

  JOIN catalog.environment_variables ev ON ev.environment_id=e.environment_id

  WHERE CAST(ev.value AS VARCHAR(1000))

  LIKE 'Data Source%'

  when i check through objectparameters table I am getting correct value but I need to use environment variables table so if any variable get change then it pick automatically. find below query which give me correct result.

  SELECT DISTINCT p.name AS ProjectName,er.environment_name AS EnvironmentName,

  design_default_value

  FROM catalog.projects p

  JOIN catalog.object_parameters op ON op.project_id=p.project_id

  JOIN catalog.environment_references er ON p.project_id=er.project_id

  JOIN catalog.environments e ON e.name=er.environment_name

  WHERE CAST(op.design_default_value AS VARCHAR(1000)) LIKE '%Data Source%'

  AND op.object_type=20

  If anyone know how to get same thing from environment variables table please help

• Phil-94129

  Mr or Mrs. 500

  Points: 582

  More actions

  July 27, 2017 at 9:23 am

  #1953056

  I had a similar requirement, to identify each variable, for each package, for each environment.   Please see the query below if see if it helps:

  Use SSISDB
  select
       e.[name] as 'Environment Name'
       ,op.object_name as 'Package Name'
       ,ev.[name] as 'Variable Name'
    ,ev.[value] as 'Variable Value'
  from catalog.environment_variables ev
      Join [catalog].[environments] e on ev.[environment_id] = e.[environment_id]
      join [catalog].[object_parameters] op on ev.name = op.referenced_variable_name
      order by ev.[environment_id]
          ,op.object_name
          ,op.parameter_name
          ,ev.[name]