Find SSIS Package last modified/deployed date - SQL Server

Question

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

Solution 1

In SSISDB you can see which VERSION_BUILD was EXECUTED at a particular time. If you use a code repository (TFS?) you can find which version of the package has that version_build, and when that version was stored in the repository. That is the modified date you want to know.

The SSIS part of the sql statement you need could be thus:

use SSISDB

select  top 50 
         xs.execution_path
        ,cast(xs.start_time as datetime2(0)) as start_time
        ,x.project_version_lsn
        ,p.version_build
from    internal.executables x
join    internal.executable_statistics xs on x.executable_id = xs.executable_id
join    internal.packages p 
                on  x.project_id = p.project_id 
                and x.project_version_lsn = p.project_version_lsn
                and x.package_name = p.name

where   x.package_name = 'Package1.dtsx'
and     x.executable_name = 'Package1'

order by start_time desc

How to query your code repository is your next challenge.

In the code view of the package, in the top 20 lines, you will find something like:

DTS:VersionBuild="62"

If you only need to know what the latest deployed version is, regardless of whether it was actually executed, you can query:

select  max(version_build) as latest_version_build
from    internal.packages
where   name = 'Package1.dtsx'

Solution 2

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.

Solution 3

There are few ways as per this answer:Can I tell the last modified date on an SSIS package from Integration Services.Posting on SO as well, as community wiki

If the packages are stored in MSDB then no, the only date/time stamp is the creation date. Modified date is not tracked. You would need to delete the package prior to redeploying in order to track a modified date, which is essentially just forcing the create date to be your redeploy date.

In SQL 2005, the metadata for packages stored in MSDB is stored in msdb.dbo.sysdtspackages and msdb.dbo.sysdtspackages90 so you can run a SELECT on one of those tables to see your packages. For SQL 2008 and beyond, the table name is sysssispackages.

SELECT * from msdb.dbo.sysdtspackages90 WHERE name='mypackagename'

If your package location on the server is File System then you can get the modified date on the package file via Windows Explorer (or whatever file system tool you want to use). The path for SQL 2005 is [install drive]:\Program Files\Microsoft SQL Server\90\DTS\Packages. Replace the 90 with 100 for SQL 2008, or 110 for SQL 2012.

Otherwise, I think your best bet would be to make sure security on your production server is adequate such that only those who should be deploying packages are, and then to track your publishing dates in your dev environment (source control, whatever). Doesn't really do much to ensure that production isn't changing without knowing about it, though....

Solution 4

If you use SQL Server 2012 (or later) and deploy your Package in SSISDB then following code should work.

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

View more solutions