Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Find SSIS Package last modified/deployed date - SQL Server

I would like to find last modified date of SSIS package by executing SQL Query. For example below query returns last modified date of procedures in desc order. I am expecting same kind of query for SSIS Package. Is that possible to get those information from DB?

select 
    name,
    create_date,
    modify_date 
from sys.objects 
where type='P' 
order by modify_date desc
like image 974
Ask_SO Avatar asked Oct 23 '17 08:10

Ask_SO


1 Answers

I am not allowed to comment to the last answer but in SQL Server 2016 since packages can be deployed individually, as well as being deployed as a full project, if you use

USE SSISDB
GO
SELECT MAX(p.last_deployed_time) AS last_deployed_time
FROM   [internal].[projects] p
       INNER JOIN [internal].[packages] pk ON pk.project_id = p.project_id

Then you will not get the right answer always because you are looking at the project deployed date.

Tracking of versioning for SSIS packages appears to be at the project level in 2016 rather than at the package level, so I do not know if there is a way to find the exact date a particular package was deployed.

Please see https://www.timmitchell.net/post/2016/11/11/ssis-catalog-project-versioning/ for some good information on this.

like image 60
Dorothy Hawley Avatar answered Sep 28 '22 07:09

Dorothy Hawley