SSIS Inserts 01/01/1753 instead of NULL for datetime

Question

SSIS Inserts 01/01/1753 instead of NULL for datetime

Brandie Tarvin

SSC Guru

Points: 173061
More actions
November 7, 2008 at 5:05 am

#124203

Has anyone seen this behavior?
I upgraded a DTS package to SSIS and suddenly the new package is taking NULL dates from a csv file and pushing them into SQL 2005 SP2 as '01/01/1753'. This is NOT the expected behavior. In SQL 2000, everything worked fine. No entry in the file (such as &ltdelimiter&gt&ltdelimiter&gt) became NULL in the database. Not so now that I've upgraded though.
I want the database to take in these values as NULLs, not put in the minimum date on the datetime column. And it doesn't make sense to me why this is happening considering the column on the table is set to NULL.
Any thoughts?
Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
December 6, 2023 at 9:49 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Where in the package is it actually becoming a date? Check the input and output collections for the source component.

If the actual value in the csv file is an empty string something is finding the string and converting it to a date. If you have to, enter an invalid date into the column and run the package to see which component fails. I would suspect that the conversion is making empty string into 1/1/1753 rather than NULL. There is really no way in a CSV file to distinguish one from the other.

So, if the conversion happens after the source of the data flow, you can add a derived column component and handle the conversion from empty string to NULL yourself. If the conversion is happening in the source component (either the input or the output of the source component is actually a date data type) you may have to actually look for that date with a derived column component and change it to NULL.

Why the difference from DTS? Implicit conversions don't get handled the same way from programming language to programming language so it may simply be a .Net thing.

Brandie Tarvin SSC Guru Points: 173061 More actions · Answer 2

It is happening after the source, which is why it's driving me crazy. I only have a source & a destination, nothing in between. So you're probably right. It sounds like instead of reading the stupid thing as a NULL, the SSIS is reading it as a blank.

Damn. That's a lot of csv files & a lot of conversion tasks I'm going to have to add to fix the issue. I hate that.

ARGH!!!!!!!!!! Why can't this single thing behave the way it did in DTS??? (<---rhetorical & obligatory rant) ARGH!!!!!!!!

Thanks, Michael.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Brandie Tarvin SSC Guru Points: 173061 More actions · Answer 3

OH, DOH!!!!

RetainNulls property is set to FALSE for all the flat file sources.

DOH!

Easy fix. @=) Happy now. @=)

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

pmidgette Newbie Points: 1 More actions · Answer 4

Thanks!

This helped me 13 years after you posted it!

Brandie Tarvin SSC Guru Points: 173061 More actions · Answer 5

pmidgette wrote:

Thanks!
This helped me 13 years after you posted it!

So glad it did.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.