October 13, 2017 at 9:22 am
Dear all,
Can you please let me know how can I get the names of all the SSIS packages that I have ?
Thank you for the support.
October 13, 2017 at 9:24 am
When I try with
select * from msdb.dbo.sysssispackages
I get this:
SqlTraceCollect
SqlTraceUpload
TSQLQueryCollect
TSQLQueryUpload
PerfCountersCollect
PerfCountersUpload
QueryActivityCollect
QueryActivityUpload
Which is not the name of my packages
October 13, 2017 at 9:30 am
river1 - Friday, October 13, 2017 9:22 AMDear all,
Can you please let me know how can I get the names of all the SSIS packages that I have ?
Thank you for the support.
Check your VCS.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 13, 2017 at 9:45 am
What deployment method have you used. MSDB, SSISDB or file system (I really hope not the latter!).
If SSISDB, try:USE SSISDB;
GO
SELECT p.name AS ProjectName, pp.name AS PackageName
FROM internal.projects p
JOIN internal.packages pp ON p.project_id = pp.project_id;
If you have versioning on your Instance, you'll need to filter these. For example:USE SSISDB;
GO
WITH Packages AS(
SELECT p.name AS ProjectName, pp.name AS PackageName,
ROW_NUMBER() OVER (PARTITION BY p.name, pp.name ORDER BY pp.version_build DESC) AS RN
FROM internal.projects p
JOIN internal.packages pp ON p.project_id = pp.project_id)
SELECT *
FROM Packages
WHERE RN = 1
ORDER BY ProjectName, PackageName;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 16, 2017 at 9:14 am
Thank you very much.
When I execute the query (first one) I get below errror:
Msg 229, Level 14, State 5, Line 7
The SELECT permission was denied on the object 'packages', database 'SSISDB', schema 'internal'.
What are the required permissions for this in the SSIS DB?
Thanks.
October 16, 2017 at 9:16 am
river1 - Monday, October 16, 2017 9:14 AMThank you very much.When I execute the query (first one) I get below errror:
Msg 229, Level 14, State 5, Line 7
The SELECT permission was denied on the object 'packages', database 'SSISDB', schema 'internal'.
What are the required permissions for this in the SSIS DB?
Thanks.
For that query? The db_reader role or SELECT permissions on the internal schema on your SSISDB database.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 16, 2017 at 10:58 am
Thom A - Monday, October 16, 2017 9:16 AMFor that query? The db_reader role or SELECT permissions on the internet schema on your SSISDB database.
'Internal', I think 🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply