Replace NUll values with Space in SSIS 20008

Question

Post reply

Replace NUll values with Space in SSIS 20008

Shaun2012

Hall of Fame

Points: 3214
More actions
August 12, 2013 at 2:29 pm

#281839

HI, I am dumping data from source to destination but ..problem is some values in source table have null values but in the destination table, it does'nt allow null values. So for those values, i need to put space for those values.. Any idea how to do it and where to put it.
regards
shaun...

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

You can use a derived column to replace the NULL values:

(ISNULL(mycolumn)) ? " " : mycolumn

Or if the source allows it, change it in the query.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Shaun2012 Hall of Fame Points: 3214 More actions · Answer 2

thank u its working but how to do for date column..

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

You can't put a space in a date column.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Shaun2012 Hall of Fame Points: 3214 More actions · Answer 4

i am still getting error.. as i moving oracle data from one source to destination. and destination columns will not accept null values..so any idea how to move data for this kind of issue.

here one of the error i am getting:

[BI [82]] Error: Fast Load error encountered during Load phase. Class: Logical

Status:0Code: 0

Note:

At: ORAOPRdrpthEngine.c:1128

Text: Conversion error on column '"LASTUPDOPRID"'.ORA-01400: cannot insert NULL into (LASTUPDOPRID)

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

Do not put NULL values in the column?

Either change them or redirect them to another destination.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

richard.noordam SSCrazy Points: 2691 More actions · Answer 6

I would either:

A) change the destination table to allow nulls and then put a default value constraint on it so when it encounters nulls, it places a set value in for you (for each field you don't want nulls in)

or

B) look at the NVL function on oracle, to handle the replacement.

http://www.techonthenet.com/oracle/functions/nvl.php

Your approach is going to be somewhat dictated in how you are transfering the data.

do you:

a) select data out to a file from oracle, and then load to SQL server

or

b) use a linked server to grab the data.

It is entirely possible that with a linked server you could use the NVL in the query but i would have to test to be certain.

just some thoughts.