Sql-server – tell the last modified date on an SSIS package from Integration Services

date-modifiedsql serverssis

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.

Best Answer

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

Related Solutions

Sql-server – The package failed to load due to error 0xC0011008 Error Loading from XML

Well, it turns out this was an authorization or identity issue. My application was setting Thread.CurrentPrincipal to a custom IPrincipal instance on every request. Below is a paraphrasing of the code:

Public Class AppPrincipal
 Implements IPrincipal

 ...
End Class

... Then in Global.asax.vb

Sub Global_AcquireRequestState(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.AcquireRequestState
    Thread.CurrentPrincipal = New AppPrincipal()
End Sub

My conjecture is that the ManagedDTS.dll assembly would then fail to open (or parse) the SSIS package since it could not resolve the "AppPrincipal" identity. That is my guess. The fix for this was to simply stop mutating Thread.CurrentPrincipal.

Security – SSIS package hangs on Setting Source Connection

In the SSIS package, locate the Connection Manager for the DB you are trying to connect to. On the properties panel of that connection manager, set the 'Persist Connection' property to TRUE. This will maintain an active connection to the database server through the entire life of the package instead of only creating a connection for a specific task. It's a long shot, but worth a try.

Best Answer

Related Solutions

Sql-server – The package failed to load due to error 0xC0011008 Error Loading from XML

Security – SSIS package hangs on Setting Source Connection

Related Topic