SSIS import from excel - Dates being NULLIFIED

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 2005 Forums

SSIS and Import/Export (2005)

SSIS import from excel - Dates being NULLIFIED

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-09-12 : 09:31:38

I have a spreadsheet with some data:

9999-99-99
9999-99-99
06/07/2008
9999-99-99
9999-99-99

For some reason, the actual valid date is imported as NULL. I've tried to transform into a date field, and have tried all sorts of things, but to no avail. Can anyone explain why thsi happens and how I can prevent the NULLification?

Thanks

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-09-12 : 09:56:27

What I have done:

-Export from excel task
-Data conversion task - converting date to ANSI string
-Derived column task with the following logic: col1 == "9999-99-99" ? "1900-01-01" : col1 - OUTPUT is a DATE datatype
-OLE DB import task to a test table with datetime datatype

This is the imported test data:
Stocked
Start
9999-99-99
06/07/2008
06/07/2008
06/07/2008
9999-99-99
9999-99-99
9999-99-99
06/07/2008
9999-99-99

This is the output from the table:

1900-01-01 00:00:00.000
NULL
NULL
NULL
1900-01-01 00:00:00.000
1900-01-01 00:00:00.000
1900-01-01 00:00:00.000
NULL
1900-01-01 00:00:00.000

Hearty head pats

favaz.farook@gmail.com
Starting Member

8 Posts

Posted - 2008-09-12 : 10:28:27

In the Data Flow tab used a "Excel Source Task" and mapped the Excel, the used a "OleDB Destination Task" with pointing to a tables to store the Excel Values with same data as you specified it worked fine :)

i could see the date stored in the table (column type in table is datatime)

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-09-12 : 10:34:35

Try this:
[col1] == "9999-99-99" ? (DT_DATE)(DT_WSTR,20)(DT_DATE)"1900-01-01" : (DT_DATE)(DT_WSTR,20)(DT_DATE)[col1]

Output:


9999-99-99	> 1/1/1900 12:00:00 AM
06/07/2008	> 6/7/2008 12:00:00 AM
06/07/2008	> 6/7/2008 12:00:00 AM
06/07/2008	> 6/7/2008 12:00:00 AM
9999-99-99	> 1/1/1900 12:00:00 AM
9999-99-99	> 1/1/1900 12:00:00 AM
9999-99-99	> 1/1/1900 12:00:00 AM
06/07/2008	> 6/7/2008 12:00:00 AM
9999-99-99	> 1/1/1900 12:00:00 AM

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-09-12 : 11:10:04

Hi

I think it could be something to do with the spreadsheet that I receive. I first thought it was something to do with the date format in excel. However, I created a new column, typed in the same date value, formatted the date as the same format (date) and it inserts the data???

If it is the excel data, then why, when I create the exact same condition in a new column, the data is inserted?

I have no control over what I receive, therefore, cannot change the input.

Has anyone else experienced such behaviour?

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-09-12 : 11:36:11

ok, this is getting stranger and stranger.

If I copy the column into an adjacent column, I get the same import issue. If I manually type in the value and format the column as the same as the adjacent value, it inserts it with no issue.

If I then paste the value over the column I manually entered into, it imports fine.

So I think it is definately something to do with the spreadsheet????

Hearty head pats

favaz.farook@gmail.com
Starting Member

8 Posts

Posted - 2008-09-12 : 12:09:11

if you do not mind can you mail the Excel to favaz.farook@gmail.com i would like to experience it and come up with a solution

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources