I recently stumbled across a nuance of the SSIS expression language which, when you think about, kinda make sense – but it does help to be aware of it. Its concerned with casting of datetime values using the SSIS expression language
Take the following expression:
That expression casts a datetime value into a string value. If I evaluate that with my OS Regional Settings set to English (United Kingdom) I see this:
If I set my OS Regional Settings to English (United States) I see this:
Note how that simple change to the regional settings has caused the result of my expression to change. This could have dangerous consequences; for example, if you are using the result of this expression in a dynamically built SQL statement (as I was) then one of two things will happen, either you will get the wrong result or you’ll get an error. Observe how, n my dynamically built SQL statement, I’m CONVERTing a string literal (which is constructed using the above expression) to a datetime value:
however with a simple change of my regional settings to English (United States) I see this:
and when you run that particular SQL statement in SSMS:
it blows up!
Definitely one to be aware of! Watch those Regional Settings and their affect on casting of dates in the SSIS expression language!
What should you do instead?
If you need a failsafe way of constructing a date that doesn’t rely on Regional settings then consider something like the following:
(DT_WSTR,4)YEAR( @[System::ContainerStartTime] ) + "-" +
RIGHT("0" + (DT_WSTR,2)MONTH( @[System::ContainerStartTime] ), 2) + "-" +
RIGHT("0" + (DT_WSTR,2)DAY(@[System::ContainerStartTime] ), 2)
That expression will build a date string with format YYYY-MM-DD (which is the ISO-ratified unambiguous way of representing a date) regardless of Regional Settings: