January 27, 2010 at 12:42 pm
I have an SSIS package importing text file data in delim format to a table. I am running into an issue with the data where the column in question is a date field, but sometimes the value is a series of spaces insead of a date (sample only on lillions of rows in the file). So I created a Transformation step to address the spaces and converts them to NULL value (which is what the business requires), but when converting the data from STRING to DATE and writing it to the table (datetime) field (allows NULLS), I get an error:
[Data Conversion [4221]] Error Data conversion failed while converting column "datecolumn" (50) to "datecolumn" (4238). The conversion returned status vlaue 2 and status text "The value could not be converted because of a potential loss of data."....
The process is this:
Flat File to matching cloumns. Date fields are string(50) output fields.
The date fields are then run through Derived Column to eliminate spaces and replace them with NULLs (LEN(TRIM([datefield])))>0?[datefield]:NULL(DT_WSTR,50)
Then the process passes this output colmn to a data conversion task where the value is converted to DT_DATE. This is where it breaks. However, when I eliminate the task and write the value to the table (Datetime column), same error.
I beleive I need to use event handling to get a look at the erroneous data or at least what it is trying to write where, but I have not used event handlers yet.
Any help would be appreciated.
Thank You In Advance.
J.T.
January 27, 2010 at 1:36 pm
GOT IT!!!!
There was other data beside blank spaces in the field where the date was expected. Sometimes there was also a zero ("0"). That would make my formula (LEN(TRIM(datefield)))> 0 false. I changed my formula to >1 and now it works like a charm.
It is always in the last place you look.
:blush:
January 27, 2010 at 1:39 pm
Jason Tontz (1/27/2010)
It is always in the last place you look.:blush:
Naw, I keep looking just to be sure! 😉
Glad you were able to fix the problem!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply