SSIS IsNumeric expression Error

Question

Hi am using following exression in ssis package !ISNULL((DT_I4)Route) ? (DT_WSTR,50)("SB" + SUBSTRING(RIGHT(Route,2),1,1)) : (DT_WSTR,50)Route

when the Route value is Numeric it is sucess, when it is Non-numeric failing with following description. Any help,how to resolve this issue

[Derived Column [111]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (111)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "column_New" (679)" 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.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Derived Column" (111) failed with error code 0xC0209029 while processing input "Derived Column Input" (112). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

Answer 1

The following part of your conditional statement "(DT_I4)Route" is attempting to cast Route as a 4 byte Integer. You do not need to cast Route to a DT_I4 to test for Null.

EDIT --
I had missed that you were trying to test for non-numeric. So here is how I would do this:

In your derived column transform have one column that has the expression (DT_I4)Route and one column where the expression is (DT_WSTR,50)Route
On the Column that is derived from (DT_I4)Route set the Error Output to Redirect Row

On the Error Output flow have a second Derived Column that uses the expression (DT_WSTR,50)("SB" + SUBSTRING(RIGHT(Route,2),1,1)) that is named the same as your column from the first derived column transform, and use a Union ALL to merge them back together.

From my testing, the expression you used returns null as soon as you encounter an error (and casting an non-numeric to a DT_I4 is an error) but only if you have your error output set to something other than fail component. Ignore Failure won't work since you will then just get null for the whole expression where the value is non-numeric.