Access system date in SSIS without a Script Task

In the world of ETL, it’s quite common to need to know the current date, especially without the time component. Because Script Tasks allows us to code up anything in .NET, it should be fairly obvious how to generate and expose the current date. for example:

Dts.Variables["TheCurrentDate"].Value = DateTime.Date;

That little snippet of code will store the current date into a variable named “TheCurrentDate”. This is straightforward and there’s absolutely nothing wrong with the approach, however, there’s a simpler way to fetch this information without the need of adding a whole control flow item for such a minute amount of work. It’s brilliantly simple, only requires the creation of a variable, and can be added in about 10 seconds:

1. Create new variable with Data Type = DateTime
   1. Set property EvaluateAsExpression = True
   2. Set property Expression =

      DATEADD( "day", DATEDIFF( "day", (DT_DATE) "1900-01-01", @[System::StartTime] Â ) , (DT_DATE) "1900-01-01" )
      

That’s it! The variable will contain the package start time with the time truncated, making it easier to compare dates directly. In fact, this method can be used to truncate the time portion of any DateTime Data Type. Simply replace @[System::StartTime] with another variable.

Want to know how it works? The inner DATEDIFF() function calculates the number of days since 1900. This value is supplied to the outer DATEADD() function, adding days forward from 1900, resulting in a DT_DBTIMESTAMP with the time rounded off. And we accomplished it with pure math—no messy string manipulation required! I borrowed the idea from the same expression in T-SQL: DATEADD(day, DATEDIFF(day, 0, getdate()), 0).

There’s one caveat to this: Because SSIS doesn’t have a native date-only variable type, the value technically contains the date at midnight. However, many tools—such as SQL Server—will implicitly convert to the requisite data type, making “2011-09-28 12:00:00 AM” equal to “2011-09-28″