Are you convinced you require a round trip to the database server at all?

If your example reflects the true need and the SSIS Package runs on the same machine as the database instance, or at least the same time zone, then a SSIS Variable that takes its value from an Expression like this is all you need:

@[User::YourDateVariable] == "19000101" ? GetDate() : @[User::YourDateVariable]

Where @[User::YourDateVariable] is the variable you were substituting into your SQL statement.

PS Note, you may need to tweak your data types when using an expression this way to compare strings and then returning a datetime from GetDate().