Order in which DTS Packages are displayed from SQL Server 2005 GUI

  • Article

Earlier this year somebody raised the following concern:

 

From: <Intentionally_Removed>
Sent: Wednesday, April 18, 2007 10:23 PM
To: <Intentionally_Removed>
Subject: SQL 2005 : DTS Packages order of display...

We noticed the following when trying to open DTS Packages in SQL 2005. It seems the order of display for all the versions of the package is MM DD YYYY. It is very confusing to choose the latest version of the package.

clip_image001

 

And this was my answer to it:

 

From: Ignacio Alonso Portillo
Sent: Thursday, April 19, 2007 9:47 AM
To: <Intentionally_Removed>
Subject: RE: SQL 2005 : DTS Packages order of display...

BTW, just for the sake of completeness. The reason why SQL Server 2000’s Enterprise Manager showed the dates with YYYY MM DD format regardless of the regional settings, is because in SQL2K the dialog showing the DTS package versions used the SQLServer DMO object (http://msdn2.microsoft.com/en-us/library/aa276064(SQL.80).aspx) and didn’t set its RegionalSetting property (http://msdn2.microsoft.com/en-us/library/aa260535(sql.80).aspx) to TRUE. The default value for the SQL_SOPT_SS_REGIONALIZE is SQL_RE_OFF (false), so the driver doesn’t convert date, time, and currency data to character string data using client locale setting.

On the other hand, the new implementation in SQL Server 2005, stores the date in a DateTime structure (http://msdn2.microsoft.com/en-us/library/system.datetime.aspx) and to render it on screen, it calls the ToString method without arguments (http://msdn2.microsoft.com/en-us/library/k494fzbf.aspx), so it uses the formatting information derived from current culture.

Thanks,

Nacho

From: Ignacio Alonso Portillo
Sent: Thursday, April 19, 2007 12:38 AM
To: <Intentionally_Removed>
Subject: RE: SQL 2005 : DTS Packages order of display...

In the Regional Options, choose the short date format of your preference, and make sure you re-start the instance of devenv.exe from which you were designing the SSIS package, so that the change takes effect. That will do it.

Thanks,

Nacho

 

Which clearly explains the reasoning behind that behavior.

 

Hope this post was helpful, or at least entertained you a bit. :-)