How can I make sysdate variable in VS 2019 SSIS

Question

I want to make a variable within SSIS that is the current date so that I can reference it in a script task but I have only been able to do this with start date and creation date instead of sysdate. Can anyone help?

Answer 1

SSIS has two states: design-time and run-time. Design-time is the experience in Visual Studio/BIDS/SSDT. There are artifacts on the screen, interactive windows, and our Variables window show the values of the package "at rest".

The Run-time is the experience in the Debugger (or an unattended execution). In the debugger, it looks like the run-time - you see the objects, the data flow components light up and you can see data flowing between components but you can find discrepancies between the two. For example, the Variables window won't show you what the value of a variable is "RIGHT NOW." Instead, it is going to show the design-time value. If you want to see what the internals look like now, that's the Debug menu, Locals window. There you'd see that the current values of all the variables that were defined as design-time.

The System::StartTime has the run-time value set when the package begins (OnPackageStart event). The time the package starts is constant for the run of a package, whether the package run lasts a minute or 3 days, the start time is the time the package started. The design-time value won't ever be passed to a consumer of that variable because the value was updated when the package starts. SSIS does not update the design-time values with the previous run's values. i.e. A design-time start time of 2021-02-18 will always be the at rest value despite being run every day

You cannot control this behavior, nor do you need to worry about it never being accurate as it is part of how run-time works.

An expression exists, GetDate() which is evaluated every time it is inspected (design and run time). I usually advise against this because I am likely using the current time to correlate database activities.

e.g. I created these 10, 100, or 1000000 records at 2021-02-22T11:16:32.123. If I inserted in batches of ten, the first scenario would be recorded under the same timestamp. The second would look something like the first 10 at 2021-02-22T11:16:32.123, the next 10 at 2021-02-22T11:16:32.993, the next ten at 2021-02-22T11:16:33.223 etc. Maybe more, maybe less. Why that matters is I can't prove to the business "these 10/100/1000000 are the rows from load X because they all have the same timestamp" Instead, I need to find all the rows from 2021-02-22T11:16:32.123 to 2021-02-22T11:16:38.532 and oops, a different process also ran in that timeframe so my range query now identifies (10/105/1000003) rows.

GetDate for longer running processes that start before, but near the midnight boundary can result in frustrating explanations to the business.

Finally, since you're referencing a Script Task, you're already in .NET space so you can use Now/Today in your methods and not worry about passing an SSIS variable into the environment.