Issue with 12/30/1899 in flat file import in SSIS ..

Question

Issue with 12/30/1899 in flat file import in SSIS ..

SQL Kidu

Ten Centuries

Points: 1296
More actions
March 29, 2011 at 8:04 am

#247925

Hi Friends,
I have a package which imports customer information into a table from flat file.
The file has a birth date field which may contain nulls.
If this filed contains an invalid date, that record should be routed to error log file.
If not it should get loaded in the table.
A validation routine is added like this.
Dim birthDate As Date
If itemList(4).Trim.Length > 0 Then
If Not Date.TryParse(itemList(4).Trim(), birthDate) Then
isErr = True
birthDate = Nothing
WriteError("Invalid BirthDate " + itemList(4).Trim())
End If
Else
birthDate = Nothing
End If
Birth date is in the 4th position in the file
But if the field contains null values, I can see 12/30/1899 in the birth date column in the table.
How can we handle this ?

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

i think you also need to test if the date is less than a minimum acceptable value ..in your case what's the minimum acceptable date you are looking at?

Dim birthDate As Date

dim DateMinValue As Date= cDate("1900-01-01")

If itemList(4).Trim.Length > 0 Then

If Not Date.TryParse(itemList(4).Trim(), birthDate) Then

isErr = True

birthDate = Nothing

WriteError("Invalid BirthDate " + itemList(4).Trim())

ElseIf CDate(birthDate) <= DateMinValue Then

isErr = True

birthDate = Nothing

WriteError("Invalid BirthDate " + itemList(4).Trim())

End If

Else

birthDate = Nothing

End If

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQL Kidu Ten Centuries Points: 1296 More actions · Answer 2

Thanks dear. But still does the same. Showing that default date in table

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

In the Flat File Source, did you select the checkbox for "Retain null values from source as null values in the dataflow"?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

annjunk Old Hand Points: 335 More actions · Answer 4

I know this is an old topic, but this seems to be a known "feature" with flat files with empty strings going into a SQL table in SSIS.

The work around I found on the web was to import the info from the flat file as a unicode string, then use some sort of logic (either derived column or in your script) to find the empty strings and replace them with a NULL of a date data type and for your else, convert the actual value to a date.

I hope this helps. I found the work around here:

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

annjunk (6/30/2014)
I know this is an old topic, but this seems to be a known "feature" with flat files with empty strings going into a SQL table in SSIS.
The work around I found on the web was to import the info from the flat file as a unicode string, then use some sort of logic (either derived column or in your script) to find the empty strings and replace them with a NULL of a date data type and for your else, convert the actual value to a date.
I hope this helps. I found the work around here:

Again, wouldn't it be easier to just tick the checkbox for "Retain null values from source as null values in the dataflow"?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

annjunk Old Hand Points: 335 More actions · Answer 6

Koen Verbeeck (6/30/2014)
annjunk (6/30/2014)
I know this is an old topic, but this seems to be a known "feature" with flat files with empty strings going into a SQL table in SSIS.
The work around I found on the web was to import the info from the flat file as a unicode string, then use some sort of logic (either derived column or in your script) to find the empty strings and replace them with a NULL of a date data type and for your else, convert the actual value to a date.
I hope this helps. I found the work around here:
Again, wouldn't it be easier to just tick the checkbox for "Retain null values from source as null values in the dataflow"?

Thank you for taking the time to reply again. I somehow missed that the first 10 times through doing this.

This will NULL every blank value, not just the dates (which was what we were specifically trying to work around), but it definitely works for a much easier solution.

Thanks again.