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.
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
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy