Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Derived Column Error Expand / Collapse
Author
Message
Posted Wednesday, August 29, 2012 4:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, February 9, 2014 8:27 PM
Points: 198, Visits: 223
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



Post #1351513
Posted Wednesday, August 29, 2012 9:41 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:54 PM
Points: 3,087, Visits: 2,686
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




Post #1351993
Posted Wednesday, August 29, 2012 10:17 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 11:06 PM
Points: 724, Visits: 353
Hi
try like below

(civilianYears == "--" || TRIM(civilianYears) == "" || ISNULL (civilianYears)) ? NULL(DT_I4) : (DT_I4)civilianYears


Post #1351994
Posted Thursday, August 30, 2012 1:42 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 5:45 PM
Points: 126, Visits: 1,020
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
Post #1352042
Posted Thursday, August 30, 2012 6:56 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, February 9, 2014 8:27 PM
Points: 198, Visits: 223
Thanks to everyone for the assistance


Post #1352548
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse