0

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?

2
  • 1
    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, 2014 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, 2014 at 0:20

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.