A little tip here for any of you building SSIS packages.
Some of you may know that I'm a big advocate of creating your own customised logging architecture through the use of eventhandlers as I have detailed here: http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx
One thing that we are doing on my current project is to record a value which we call PackageStack - its a comma delimited list of all the packages that have been called using the Execute Package Task. We record it by maintaining a user variable in the topmost package and appending the value of System::PackageName in the OnPreExecute event of each called package. That on its own is useful information.
Well, just recently SSIS guru Darren Green has joined the project and he had a really bright idea how we could improve it further. As well as appending System::PackageName, why not append System::VersionMajor, System::VersionMinor & System::VersionBuild as well. That makes it easy to see which version of each package is deployed on each of your environments. Combine that with a source control system such as SourceSafe and you have a way of ensuring you are building identical environments for debugging and error investigation.
There is also an added advantage on our current project because we are using Cruise Control to automate our build process. Previously we ran into problems where a build did not contain the package versions that we thought it did - the technique detailed here helps us know about these problems very quickly!
Great idea Darren. Thanks buddy!
-Jamie