June 7, 2010 at 3:05 am
Hi
Facing some difficulty in loading SQL Table from excel file.
My source file is excel and it has one column (col1) of date data type. When inserting data into SQL table using SSIS it passes error since most of the rows are empty in that column and few have data.
Eg sample data (col1 is date formatted in excel file)
Col1 col2 col3row_id
asdsadasdfdsa1
01/01/1991sadffghg2
asdfhjg3
dfgfjhkjh4
01/01/1998rftyhuytuy5
01/01/1998qwqwqwqwee6
jkjlkqwqwee7
01/01/1998asxabvcnbm8
In destination table col1 is date datatype.
When I do simple load using data flow task it passes error. I have also used derived column and tried converting but it always error’s
Out
Have tried using
(DT_STR,50,1252)Col1 in derived column and also
(DT_DATE)(SUBSTRING(col1,1,2) + "-" + SUBSTRING(col1,4,2) + "-" + SUBSTRING(col1,7,4))
The errors I get are either
The value could not be converted because of a potential loss of data.
Or
An error occurred while attempting to perform a type cast
Can anybody help here.
June 7, 2010 at 4:54 am
Your same data doesnt look good.
Can you send some actual data to look into
June 7, 2010 at 5:29 am
Col1 Col2 Col3 row_id
- diagnosis USER1 1
- diagnosis2 User2 2
01-01-1998 ENT User1 3
- Infection user3 4
01-01-1991 diagnosis user2 5
Hope this comes neatly
June 7, 2010 at 5:34 am
So your fields are space delimited and a non-existent date appears as a hyphen ('-')?
You'll have to bring the field in as text and then use a derived column to put it into YYYY-MM-DD HH:MM etc format - then map it directly to the destination. Convert your '-' text to a default date (1/1/1900, or whatever) or to NULL.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 7, 2010 at 5:43 am
what should use to replace "-" with NULL value.
I tried
REPLACE(col1, "-", " ") : col1
and
ISNULL(col) ? " " : col1
but no luck..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply