Replace all empty values with NULL

Question

Replace all empty values with NULL

JMI

SSChampion

Points: 11831
More actions
November 18, 2008 at 12:08 pm

#128712

I'd like to replace all empty values in a table to
NULL's.
First I used Derived Column Transformation Expression:
COL008 == "" ? (DT_STR,255,1252)NULL(DT_STR,255,1252) : COL008
But I was wondering if there is a way to
replace all values in one shot and without specifying column names.
Script component maybe?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jack Corbett SSC Guru Points: 184393 More actions · Answer 1

What is your source? Most products have a conversion like SQL Server's NULLIF or you can use CASE of IIF depending on the source to do at as part of your source query.

Another option I have used is to load the empty values into SQL Server then add an Execute SQL Task as the next step in the Flow that does an Update statement. Update table Set column = Null where column = ''

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

JMI SSChampion Points: 11831 More actions · Answer 2

Hi Jack,

Of course SQL code will solve this very easily.

But remember? My boss doesn't want to see any "Execute SQL" Tasks

in SSIS. Unfortunately....

Unless it's really has a value and is reusable.

Jack Corbett SSC Guru Points: 184393 More actions · Answer 3

Odds are with all my posts I'm not going to remember all the requirements based on the OP. Now that you mention it I do recall, but I still think it is a foolish requirement and impediment to success. You'd be done by now if allowed to use a few well placed Execute SQL Tasks.

It does have value. The value is that it will perform probably 100 times faster than looping through all your rows and changing the values 1 by 1 in SSIS. IT is also easier to read and figure out.

Like I also said you can probably do it in your source query. What is your source? I've done it with FoxPro and if you can do it with that you can do it with anything, except maybe a flat file.:D

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 4

Jack Corbett (11/18/2008)
Now that you mention it I do recall, but I still think it is a foolish requirement and impediment to success. You'd be done by now if allowed to use a few well placed Execute SQL Tasks.

The boss would be wiser to forbid the use of SSIS than to forbid "Excute SQL" tasks in SSIS.

riga1966 (11/18/2008)
Hi Jack,
Of course SQL code will solve this very easily.
But remember? My boss doesn't want to see any "Execute SQL" Tasks
in SSIS. Unfortunately....
Unless it's really has a value and is reusable.

Write the WHILE loop for your boss... have him/her test it on a million rows. Then give him/her the equivalent simple set-based UPDATE and have him/her test it again. The boss [font="Arial Black"]will [/font]see the "real value".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)