Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 GETDATE()).

Is there an elegant way to do this?

share|improve this question
Your packages have a Variable which, is used to derive date strings and is based on GetDate. The desire is to allow this to be a user defined value in testing but default back to current date for production? How exactly do you assign the value? Do you use an Expression on the Variable or do you use a Script Task to assign the value. Do you really need GetDate or will the start of the package suffice as a point in time reference? – billinkc Jan 14 '14 at 15:42
Most of the packages can build the date string as an expression of a variable. I suppose the start of the package could work but I don't see how that makes a difference since it seems they should be the same. – user27810 Jan 15 '14 at 0:20

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.