Can an SSIS variable have a default value

ssis

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?

Best Answer

you should be able to do it quite easily like this:

  1. Bring the variables editor up & add a variable to contain your preferred date
  2. Inspect the variable property window
  3. Choose the ellipsis for the Expressions property to get the expression editor
  4. Define your date output using getdate() functions etc. (reasonable subset of T-SQL fns are present)
  5. The result will be shown in the variables pane (value) & re-evaluated at runtime
  6. For testing, remove the expression & replace with a fixed value of your choosing
  7. Get really clever & add separate debug_date & debug_mode variables that you can use in the expression to switch between the expression & your fixed value depending which mode you are in.
Related Question