How to know when an SSIS package is updated in SQL 2005?

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

Home Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Recent Posts

Popular Topics

Home

Home » SQL Server 2005 » Administering » How to know when an SSIS package is updated...

How to know when an SSIS package is updated in SQL 2005?

Expand / Collapse

Author

Message

Posted Friday, July 03, 2009 6:37 PM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, October 13, 2009 3:09 PM
Points: 1,357, Visits: 1,020

Many thanks in advance.

Post #747134

Posted Saturday, July 04, 2009 5:44 AM

SSChasing Mays

Group: General Forum Members
Last Login: Today @ 4:19 AM
Points: 712, Visits: 11,524

do you keep the SSIS packages on the file system or in MSDB? if you keep them on the file system then the modified date of the package will change with each new release. you would normally keep SSIS packages in sourceSafe or a similar if you have multiple devs working on the same packages, then you can use the inbuilt version tracking in those tools.

if they are stored in MSDB im sure you must be able to query one of the system tables, im not sure which ones they use without looking but im sure there will be a modified date on one of them that you could query or stick a trigger on to mail you.

Post #747194

Posted Monday, July 06, 2009 7:53 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, October 13, 2009 3:09 PM
Points: 1,357, Visits: 1,020

It is saved in msdb. What I would like to know is how to retrieve the date the packages were modified.

Post #747737

Posted Tuesday, July 07, 2009 8:42 PM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 5:06 PM
Points: 1,322, Visits: 6,479

SELECT *
FROM [msdb].[dbo].[sysdtspackages90]

HTH!
MJ

Post #748982

Posted Friday, July 10, 2009 10:22 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, October 13, 2009 3:09 PM
Points: 1,357, Visits: 1,020

This script returns createdate column. Does this data tell us the date the package is updated?

Post #751233

Posted Friday, July 10, 2009 10:57 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 10:44 PM
Points: 1,430, Visits: 1,163

Internally there are several ways to detect this, one of the best IMNSHO, is VersionBuild in the package properties, it is updated everytime the package is saved (if there were any changes) and the user doesn't have to do anything. The other properties, CreateDate, MajorVersion, MinorVersion are changed by the user, there is no auto chage going on there. The only other field that is auto changed is VersionGUID, but all that would really tell you is that it is different than another saved version, not how man versions there are between. As part of my auditing process I capture all of this and write it to an audit database at the start of package execution. That way I have an indisputable way to state that a package did change and that the runs of that package before that date used a particular version and the runs after used a different version. It helped a lot with the BI work I was doing to know what package version was in play so we knew if a problem had been persisted into newer versions..

CEWII

--------------------------------
Having trouble figuring out what jobs are running in SQL Server at the same time.
Try Sql Job History Visualization
It lets you view your SQL Job history on an Outlook style calendar..

Post #751266

Posted Friday, July 10, 2009 11:42 AM

SSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 6:41 PM
Points: 1,695, Visits: 522

Though this is not my question, thanks Elliott for the answer. This will prove useful in my environment as well.

In addition to using TFS already.

Jason
Why, so Serious?

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
How Many VLFs in the Tran Log - Kimberly Tripp

Post #751295

« Prev Topic | Next Topic »

Permissions

Expand / Collapse