June 6, 2017 at 6:38 am
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
July 27, 2017 at 9:23 am
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]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply