July 3, 2009 at 6:37 pm
Many thanks in advance.
July 4, 2009 at 5:44 am
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.
July 6, 2009 at 7:53 am
It is saved in msdb. What I would like to know is how to retrieve the date the packages were modified.
July 7, 2009 at 8:42 pm
SELECT *
FROM [msdb].[dbo].[sysdtspackages90]
HTH!
MJ
July 10, 2009 at 10:22 am
This script returns createdate column. Does this data tell us the date the package is updated?
July 10, 2009 at 10:57 am
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
July 10, 2009 at 11:42 am
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...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply