I have to migrate a SQL Server 2000 database to version 2008. A large part of that work is to update all the DTS packages to SSIS. Many of the packages require using the current date to establish parameter settings as well as date-stamping files (i.e.
myoutputYYYYMMDD.csv, for example). This logic has been ported over to the new SSIS packages but the problem I'm having is now during testing.
Backups and restores from the old to the new server is a bit problematic due to its size but since the packages do archiving, I could take an older backup and run tests against that and compare package results with some archived files, in lieu of real-time comparisons.
Since this is a better logistical solution, I need to redesign the packages where any of the package variables depend on the usage of
GETDATE() and need to reference a new date variable that will be
GETDATE() once we roll out to production but needs to be a different date during testing (e.g. I restore a 1/2/2014 and then pass in 1/3/2014 to my date variable during testing but in live production use, it will be
Is there an elegant way to do this?