December 12, 2016 at 10:47 am
Hi Experts,
If I need to list out all the SSIS packages under the Integration services catalog folder how to get the details by writing simple T SQL and also the configuration details of each package?
This can greatly help me in reducing my manual effort in finding the configuration checks.
Thanks.
December 12, 2016 at 11:18 am
SQL-DBA-01 (12/12/2016)
Hi Experts,If I need to list out all the SSIS packages under the Integration services catalog folder how to get the details by writing simple T SQL and also the configuration details of each package?
This can greatly help me in reducing my manual effort in finding the configuration checks.
Package list is easy:
USE SSISDB;
GO
SELECT p.name
FROM catalog.packages p;
Please describe what you mean by 'the configuration details of each package'. What exactly are you looking for here?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 12, 2016 at 11:24 am
This gives me the list of packages, thanks for that. But each package has few configuration associated to it.
If I could get the config details, would have been excellent for me to compare with the correct data to find if there is any mistake.
Thanks.
December 12, 2016 at 11:56 am
SQL-DBA-01 (12/12/2016)
This gives me the list of packages, thanks for that. But each package has few configuration associated to it.If I could get the config details, would have been excellent for me to compare with the correct data to find if there is any mistake.
I'm still a bit unclear. Are you talking about environment references and environment variables?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 12, 2016 at 11:59 am
Yes, please..
I made a join with another few tables and found the the folder reference, along with this if could get the env details, would be easier for me to gather all the data.
USE SSISDB;
GO
SELECT @@servername, p.project_id, p.name,f.name
FROM catalog.packages p
inner join [SSISDB].[internal].[projects] pp
on p.project_id = pp.project_id
inner join [SSISDB].[internal].[folders] F
on pp.folder_id = f.folder_id
where F.name = ''
Thanks.
December 12, 2016 at 12:02 pm
Is this the correct one? I just gathered the data from the tables.
SELECT @@servername, p.project_id, p.name,f.name, ev.value
FROM catalog.packages p
inner join [SSISDB].[internal].[projects] pp
on p.project_id = pp.project_id
inner join [SSISDB].[internal].[folders] F
on pp.folder_id = f.folder_id
inner join [SSISDB].[internal].[environments] e
on e.folder_id = f.folder_id
inner join [SSISDB].[internal].[environment_variables] ev
on ev.environment_id = e.environment_id
where F.name = ''
Thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply