SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Derived Column Error


Derived Column Error

Author
Message
sqldummy79
sqldummy79
SSC Veteran
SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)

Group: General Forum Members
Points: 231 Visits: 237
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



happycat59
happycat59
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8451 Visits: 3281
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



sandeep rawat
sandeep rawat
Right there with Babe
Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)

Group: General Forum Members
Points: 793 Visits: 376
Hi
try like below

(civilianYears == "--" || TRIM(civilianYears) == "" || ISNULL (civilianYears)) ? NULL(DT_I4) : (DT_I4)civilianYears
Rock from VbCity
Rock from VbCity
SSC Eights!
SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)

Group: General Forum Members
Points: 863 Visits: 1038
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
sqldummy79
sqldummy79
SSC Veteran
SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)

Group: General Forum Members
Points: 231 Visits: 237
Thanks to everyone for the assistance



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search