August 29, 2012 at 4:52 am
I'm trying to "change" the data of a specific column during a SSIS process.
I have a text file, that I'm trying to process into a database, and one of the columns (in the text file) contains "numeric" values or an empty space or null value or "--" (two dashes). I want to "replace" the empty space or "--" with a null value (DT_I4) and convert the numeric value to an integer (DT-I4) since the column in the data base is an int field.
This is the code that I have in the expression field
civilianYears == "--" || civilianYears == " " ? NULL(DT_I4) : (DT_I4)civilianYears
However, when I run the task it keeps giving me an error message: The "Derived Column" failed because error code 0xC0049063 occurred, and the error row disposition on "Derived Column.Outputs[Derived Column Output].Columns[New]" 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.
I have a feeling it's the conversion in the "true" and "false" part but I can't figure out what it is.
Any assistance would be greatly appreciated
August 29, 2012 at 9:41 pm
A couple thoughts...perhaps the empty space string contains more (or less than one space) and also what happens when the souce column is null ?
Perhaps try....
civilianYears == "--" || TRIM(civilianYears) == "" || ISNULL (civilianYears) ? NULL(DT_I4) : (DT_I4)civilianYears
August 29, 2012 at 10:17 pm
Hi
try like below
(civilianYears == "--" || TRIM(civilianYears) == "" || ISNULL (civilianYears)) ? NULL(DT_I4) : (DT_I4)civilianYears
August 30, 2012 at 1:42 am
Nothing wrong with the previous two suggestions, but I rather prefer to check for NULL first, and TRIM() the column when checking for the double dashes, like below:
(ISNULL (civilianYears) || TRIM(civilianYears) == "--" || TRIM(civilianYears) == "") ? NULL(DT_I4) : (DT_I4)civilianYears
Cheers,
Hope this helps,
Rock from VbCity
August 30, 2012 at 6:56 pm
Thanks to everyone for the assistance
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply