How does SSIS determine the two digit year cut off

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

How does SSIS determine the two digit year cut off

Author

Topic

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2015-01-23 : 10:23:48

I am importing a flat file from a legacy mainframe system that uses a six digit date format. I am bringing it in via SSIS and using a derived column task to convert the six digit string to a datetime column using the following expression:
LEN(TRIM(CustomerRequestDate11)) != 6 || (TRIM(CustomerRequestDate11) == "000000") ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(SUBSTRING(CustomerRequestDate11,3,2) + "/" + SUBSTRING(CustomerRequestDate11,5,2) + "/" + SUBSTRING(CustomerRequestDate11,1,2))

Basically what it's doing is converting 501201 to 12/01/49 and then converting that to a datetime column. The problem is that it's going into the database as 12/01/1949 instead of 12/01/2049.

So far, I have checked the database properties and the two digit year cutoff property is set to 2049, and the conversion works correctly when I run it in ssms:
SELECT CAST('491201' AS DATE)
and I get 2049-12-01 as the output.

Any suggestions on what to check next?

Stonebreaker
The greatest obstacle to discovery is not ignorance - it is the illusion of knowledge.
-Daniel Boorstin

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-23 : 10:44:39

It's configurable:

https://msdn.microsoft.com/en-us/library/ms191004.aspx

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2015-01-23 : 10:53:54

quote:
Originally posted by gbritton

It's configurable:

https://msdn.microsoft.com/en-us/library/ms191004.aspx

Thanks, but the sql server property is already correctly set. It's got to be something else.

Stonebreaker
The greatest obstacle to discovery is not ignorance - it is the illusion of knowledge.
-Daniel Boorstin

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-23 : 11:01:46

"Thanks, but the sql server property is already correctly set. It's got to be something else."

Well, there is a default value (2049). That can be changed by someone with appropriate permissions but of course it affects the whole instance.

Otherwise you'll have to so some date math in your package.

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2015-01-23 : 11:09:12

Does SSIS use the SQL Server two digit year cutoff property? It appears not to, since the two digit conversion works correctly in management studio but not SSIS.

Stonebreaker
The greatest obstacle to discovery is not ignorance - it is the illusion of knowledge.
-Daniel Boorstin

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-23 : 12:06:57

OIC. Well, here's a discussion that looks like your problem:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4eb5012d-e35d-47ae-8d0d-83f92de9ca37/ssis-2005-two-digit-year

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2015-01-23 : 13:34:18

That's the kind of change I'm trying to avoid - there are literally hundreds of date columns in tens of SSIS packages that would have to be changed individually if I did that. The potential for bug introduction is a near certainty.

Stonebreaker
The greatest obstacle to discovery is not ignorance - it is the illusion of knowledge.
-Daniel Boorstin

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-23 : 19:23:59

Then I'd import the file as is to a SQL table, then run a query to fix the columns en masse

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2015-01-24 : 12:06:20

OK, I finally figured out the correct solution. The SSIS packages do NOT use the SQL Server two digit year cutoff property. SSIS packages use the WINDOWS regional settings to determine the two digit year cutoff. However, there's a caveat: Windows regional settings are set per user. So you have to make sure that the user the ssis package is running under has that setting set to the correct value. You can either do this by having your network admin change the domain policy, or you can log in to the specific machine using the correct user id (in my case, this was the SQL Server Agent user id because the package was being called by a job) and setting the two year cutoff setting manually. Voila', no more data coming in saying it's from 1949!

Log in as the user you want to run the ssis package under and do the following:
Go to Start>Control Panel>Clock, Language, and Region>Region and Language>Additional Settings>Date
and you will be able to set the calendar for the two digit year cutoff range.

Stonebreaker
The greatest obstacle to discovery is not ignorance - it is the illusion of knowledge.
-Daniel Boorstin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources