I'm working on importing a CSV into my DB. The data should work, it's data from another application that we used to get through a dblink that we now have to get from CSV thanks to an upgrade.
Dates look like this: 4/30/2001
I tried to do a simple import like I do with numbers and strings, and got an error, so I did a derived column.
The derived column works on the dates that are not null (formula is (DT_DATE)DTE)
The derived column however failed on dates that can contain nulls. I even tried to update the formula to (ISNULL(EDTE) ? NULL(DT_DATE) : (DT_DATE)EDTE). No success, I still get the error:
[Flat File Source - O1 [6743]] Error: Data conversion failed. The data conversion for column
"EDTE" returned status value 2 and status text "The value could not be converted because of a
potential loss of data.".
Originally, in my flat file source, the dates were set to type date, and I got an error before it even got to the derived column. I've changed that to String, and it makes it to derived column, but both boxes are red. Here's the error:
[Derived Column 1 [17270]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.
The "component "Derived Column 1" (17270)" failed because error code 0xC0049063 occurred,
and the error row disposition on "input column "EDTE" (17809)" specifies failure on error.
An error occurred on the specified object of the specified component.
There may be error messages posted before this with more information about the failure.
Has anyone seen this? Am I importing my data in wrong?
