Querying SSISDB
How to know which version of my package deployed in SSISDB?
How to know when and who has
deployed the package in my SSISDB? When you want to know the information about
your deployed packages (in SSISDB) like what all version of my package has
deployed in SSISDB, when it was deployed and who has deployed it, then you have
to query the SSISDB.
Below is the query that will give
information about the packages deployed in the SSISDB catalog and also give the
information when that folder was created and what all project and packages are
deployed by which credential.
USE [SSISDB]GO
/***** Script for SelectTopNRows command from SSMS ******/
SELECT fld.Name as FolderName
,fld.created_By_Name as folderCreatdBy
,fld.Created_time as folderCreateddate
,proj.name projectName
,proj.created_time
,proj.last_deployed_time
,proj.deployed_by_name
,proj.folder_id
,pkg.[project_version_lsn]
,pkg.[name] as Pakagename
,pkg.[description]
,pkg.[package_format_version]
,pkg.[version_major]
,pkg.[version_minor]
,pkg.[version_build]
,pkg.[version_comments]
FROM [SSISDB].[internal].folders fld
left outer join [SSISDB].[internal].[projects] proj on proj.folder_id=fld.folder_id
left outer join [SSISDB].[internal].[packages] pkg on pkg.project_id=pkg.project_id
Excellent. That's exactly what I was looking for. Thanks.
ReplyDeleteThis is huge - and almost exactly what I'm looking for. We're going to enable change-tracking on all three tables, will then be able to access a continuous deployment history for any object in the Catalog. Thanks so much!
ReplyDeleteTypo in script; last line should be:
ReplyDeleteleft outer join [SSISDB].[internal].[packages] pkg on proj.project_id=pkg.project_id
My co-worker deployed a project to SSISDB (SQL 2017 enterprise ) from VS . Day, time and all other things got updated in a new line entry. Deployed_by_name field is showing an account of a different person. Same happened when I deployed a single package.
ReplyDeleteIs this a bug or something needs to be set up for Deployed_by_name to be updated every time project or package are deployed?
Thank you!