Using Derived Column Transformation Editor to populate 0 if the incoming value is null

i have the expression. I know this is a form for text type fields. i have a numeric field. IS this not the right syntax ?

ISNULL([_per_aban]) 0: [_per_aban]
LVL 1
Auerelio VasquezETL DeveloperAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Alpesh PatelConnect With a Mentor Assistant ConsultantCommented:
In Derived column

Create nwe Column and write expression.


 (DT_NUMERIC,18,0)(ISNULL([IncomingColumn]) ? 0 : [IncomingColumn])
0
 
PortletPaulConnect With a Mentor Commented:
ISNULL ( check_expression , replacement_value )

ISNULL( [_per_aban] , 0 )

see: http://msdn.microsoft.com/en-us/library/ms184325.aspx

{+edit} That's for TSQL
are you asking for SSIS?
0
 
Auerelio VasquezETL DeveloperAuthor Commented:
yes, i'm talking about SSIS. the transformation editor
0
 
Auerelio VasquezETL DeveloperAuthor Commented:
wha't i'm trying to do, is read a .csv file. If the value is null, just replace with 0.

the incoming value is null, which causes problems getting into a numeric column (even though i allow nulls). I'm using this without error:

ISNULL(SL) ? 0 : (DT_NUMERIC,18,0)SL --On the fly conversion from string to numeric

however, when I run the package, i get the below error

[Derived Column [2719]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "component "Derived Column" (2719)" failed because error code 0xC0049063 occurred, and the error row disposition on "output column "_SL" (2791)" 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.
0
All Courses

From novice to tech pro — start learning today.