menu

Home
BigQuery Time Travel Data Orphaned Users
Python >>
Data Analysis and Visualisation UX Design
SQL >>
Tracking changes of a table using trigger SQL Practice Questions
Machine Learning with BigQuery Meta Data of Greenplum Data to fixed length format file Column level security Row level security Bulk_Logged reduce logging
Data Mesh Data Pipeline for SaaS Database Design and Performance Data Modeling Best Practices Query Compilation Process
Apps
Mobile Apps Web Apps
Gadgets
ChatGPT API with Python Deployed package History From SSISDB How to Reduce SSISDB Size
Saying NO is not Leadership Advance SQL SQL Server 2017 New Functions Finding Procedure Dependency Delete all the foreign key constraints Disabling clustered Index may create problem Analyze Script Performance- Include Client Statistics
About Me

Tuesday, 1 September 2015

Deployed package History From SSISDB


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
 
 
 
 
 
 
 
at
Labels: , , , ,

4 comments:

  1. Unknown4 November 2015 at 00:57

    Excellent. That's exactly what I was looking for. Thanks.

    ReplyDelete
  2. Unknown23 January 2018 at 09:09

    This 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!

    ReplyDelete
  3. Unknown22 March 2018 at 08:30

    Typo in script; last line should be:

    left outer join [SSISDB].[internal].[packages] pkg on proj.project_id=pkg.project_id

    ReplyDelete
  4. Irina19 February 2019 at 12:20

    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.
    Is 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!

    ReplyDelete

Subscribe to: Post Comments (Atom)