SolvedPrivate

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

Posted on 2014-09-12
4
30 Views
Last Modified: 2016-02-13
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]
0
Comment
Question by:basile
  • 2
4 Comments
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
ID: 40319608
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
 
LVL 1

Author Comment

by:basile
ID: 40319716
yes, i'm talking about SSIS. the transformation editor
0
 
LVL 1

Author Comment

by:basile
ID: 40319825
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
 
LVL 21

Accepted Solution

by:
Alpesh Patel earned 250 total points
ID: 40336383
In Derived column

Create nwe Column and write expression.


 (DT_NUMERIC,18,0)(ISNULL([IncomingColumn]) ? 0 : [IncomingColumn])
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
I have a large data set and a SSIS package. How can I load this file in multi threading?
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question