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 col3 row_id
asdsad asdfdsa 1
01/01/1991 sadf fghg 2
asdf hjg 3
dfgf jhkjh 4
01/01/1998 rftyh uytuy 5
01/01/1998 qwqw qwqwee 6
jkjlk qwqwee 7
01/01/1998 asxa bvcnbm 8
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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