Get the SSIS Package names from database
-
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.
-
When I try with
select * from msdb.dbo.sysssispackages
I get this:
SqlTraceCollect
SqlTraceUpload
TSQLQueryCollect
TSQLQueryUpload
PerfCountersCollect
PerfCountersUpload
QueryActivityCollect
QueryActivityUploadWhich is not the name of my packages
-
Have you tried querying SSISDB?
John
- river1 - Friday, October 13, 2017 9:22 AM
Check your VCS.
-
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 -
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.
- river1 - Monday, October 16, 2017 9:14 AM
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 - Thom A - Monday, October 16, 2017 9:16 AM
'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