Can I tell the last modified date on an SSIS package from Integration Services

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


Here's a great post about how to retrieve information from an SSIS package. It's helped me to find information I've needed in the past and it may be of use to you:

http://sqlblog.com/blogs/jamie_thomson/archive/2009/10/18/collecting-information-about-your-ssis-packages-ssis-nugget.aspx