Andy Leonard (blog | twitter) and I are putting together a presentation on protecting the entire ETL (Extract, Transform, Load) pipeline, specifically around SSIS. In our research on weak points along said pipeline, we've come up with an interesting question right off the bat:

Do you check whether or not an SSIS package has been modified before executing it?

If you're storing the SSIS package in the file system, this would involve something like an MD5 hash check. If you're storing it in the Package Store, this would mean extracting the package from msdb.dbo.sysssispackages and then running it through a similar hash check. Obviously, you'd want to control the verifying process and the MD5 hashes such that those who have the ability to touch those SSIS locations have a very hard time tampering with the checks and the stored known good MD5 hashes.

With respect to the Package Store, if you're thinking that you're okay because the verID (GUID) gets modified every time a package is updated, or that verBuild automatically gets incremented, realize that while dbo.sysssispackages is listed in SQL Server Management Studio as a system table, it's touchable (meaning we can issue an UPDATE statement against it).

I realize that many SSIS packages execute as scheduled processes without human intervention. That would mean such checks would need to be part of that automation. It would also mean that if a difference is detected, the process would stop and hopefully someone would be immediately notified.