5

Using SQL Server 2005 (and 2008)

With a DTS package, I can open it, and it lists the date that the package was last modified. I have some SSIS packages, and I need to know if they have been modified since our last release. Is there any way to tell when they were last modified?

If I right-click, the only option that even looks promising is "Reports", but the only option there is a general report that has the package creation date and some versioning information. I already know that the programmer didn't change any of the version defaults, so there is no useful information there.

I want the last modified date, or last imported date, or something to let me know if I need to worry about some of these being modified since our last release.

Edit: The file is stored in MSDB.

thursdaysgeek
  • 326
  • 1
  • 3
  • 10

2 Answers2

5

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

anon
  • 404
  • 1
  • 5
  • 15
squillman
  • 37,618
  • 10
  • 90
  • 145
1

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

Sean Howat
  • 1,849
  • 4
  • 20
  • 33
  • 1
    This is useful to check metadata on dtsx files themselves, but the original question was asking about packages stored within MSDB. – anon Jun 27 '14 at 13:31