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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 How does SSIS determine the two digit year cut off
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stonebreaker
Yak Posting Veteran

USA
92 Posts

Posted - 01/23/2015 :  10:23:48  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 01/23/2015 :  10:44:39  Show Profile  Reply with Quote
It's configurable:

https://msdn.microsoft.com/en-us/library/ms191004.aspx
Go to Top of Page

stonebreaker
Yak Posting Veteran

USA
92 Posts

Posted - 01/23/2015 :  10:53:54  Show Profile  Reply with Quote
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
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 01/23/2015 :  11:01:46  Show Profile  Reply with Quote
"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.
Go to Top of Page

stonebreaker
Yak Posting Veteran

USA
92 Posts

Posted - 01/23/2015 :  11:09:12  Show Profile  Reply with Quote
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
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 01/23/2015 :  12:06:57  Show Profile  Reply with Quote
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
Go to Top of Page

stonebreaker
Yak Posting Veteran

USA
92 Posts

Posted - 01/23/2015 :  13:34:18  Show Profile  Reply with Quote
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
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 01/23/2015 :  19:23:59  Show Profile  Reply with Quote
Then I'd import the file as is to a SQL table, then run a query to fix the columns en masse
Go to Top of Page

stonebreaker
Yak Posting Veteran

USA
92 Posts

Posted - 01/24/2015 :  12:06:20  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000