Derived Column Error

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » Derived Column Error

Derived Column Error

Rate Topic

Display Mode

Topic Options

Author

Message

sqldummy79

Posted Wednesday, August 29, 2012 4:52 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 07, 2013 7:55 AM
Points: 198, Visits: 205

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

happycat59

Posted Wednesday, August 29, 2012 9:41 PM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 10:54 PM
Points: 2,467, Visits: 2,059

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

sandeep rawat

Posted Wednesday, August 29, 2012 10:17 PM

Say Hey Kid

Group: General Forum Members
Last Login: Monday, May 13, 2013 4:06 AM
Points: 681, Visits: 298

Hi
try like below

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

Post #1351994

Rock from VbCity

Posted Thursday, August 30, 2012 1:42 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, March 24, 2013 2:08 AM
Points: 126, Visits: 967

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

sqldummy79

Posted Thursday, August 30, 2012 6:56 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 07, 2013 7:55 AM
Points: 198, Visits: 205

Thanks to everyone for the assistance

Post #1352548

« Prev Topic | Next Topic »

Permissions