Two Digit Years

When converting dates from strings to a date type within the pipeline, SSIS uses the Windows operating system settings to determine which century a two digit date falls within.

From Control Panel open Regional and Language Options. Click the Customize button, then select the Date tab. The top section allows you to set the year range which for which two digit dates are interpreted, proving similar functionality to the database engine configuration option two digit year cutoff.

For example if your current Windows settings are 1930 and 2029, the following table shows the input text values and the converted date value when using the Data Conversion transformation.

If you change the Windows settings to 1930 and 2030, then the output will now look like this.

If you wish to influence this behaviour, without changing the Windows settings for whatever reason, you can easily use a Derived Column transform to perform some string manipulation prior to a cast for the date conversion. For example the following expression will interpret the year as being between 1911 and 2010.

 (DT_DBTIMESTAMP) (SUBSTRING(DateString, 1, 7) + ((DT_I2)SUBSTRING(DateString, 8, 2) > 10 ? 
    "19" : "20") + SUBSTRING(DateString, 8, 2))

Saved Variables (SP1)

Variables of type Date are saved within the package XML using an ambiguous format such as dd/mm/yyyy. Prior to Service Pack 1 (SP1) when a package was loaded the value was interpreted using the Regional and Language Options for the user. Following SP1 this is no fixed as the US format mm/dd/yyyy. So what does this mean?

If you are a user of a non-US format, such as the UK dd/mm/yyyy, if you view the raw XML for your package created with a RTM machine, it will look like this:

 <DTS:VariableValue DTS:DataType="7">01/05/2005</DTS:VariableValue>

According to the UK settings this will become 1 May 2005, and this is what happened for any UK users. If you open the same package on a machine with SP1 this date is now interpreted as 5 January 2005. This change in SP1 is not (currently) documented. It is however a change for the good, as pre-SP1 any US user who opened the package would have got 1 January 2005, not the expected May date. So you can now send a package across the world without different people getting different dates as would have happened previously due to the different regional settings, it does mean any existing packages you have are potentially broken. This is only an issue for users on non-US formats.

Credit to Simon for first mentioning there was something funny going down.