How to replace the blank values to NULL for a field in SSIS

Question

Post reply

How to replace the blank values to NULL for a field in SSIS

sqlbi

SSC Eights!

Points: 901
More actions
September 20, 2010 at 9:04 am

#146971

i have package it import data from flat file in the flat for one field i have blank values i want to replace them by null.
the destination field is having decimal data type. is it possible to replace blank fields by null, if so how?

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

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

yes you can do this by using a derived column transformation.

sqlbi SSC Eights! Points: 901 More actions · Answer 2

can you provide the solution, basically the incoming field has

0.0

25.0

and i want to replace it with NULL

0.0

25.0

NULL

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 3

sqlbi (9/20/2010)
can you provide the solution, basically the incoming field has
0.0
25.0
and i want to replace it with NULL
0.0
25.0
NULL

NULL can have different meaning when it comes to EXCEL and SSIS

I would advice you to convert all non available fields to some fixed value for instanve say "-1"

Raunak J

steveb. SSC-Forever Points: 46733 More actions · Answer 4

sqlbi (9/20/2010)
can you provide the solution, basically the incoming field has
0.0
25.0
and i want to replace it with NULL
0.0
25.0
NULL

Add a derived column transformation in the data flow, connect this to the source.

open the derived column editor then select to overwrite the column with the data and then add some code to do the replace the basic structure is

REPLACE( «character_expression», «search_expression», «replace_expression» )

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

The easiest solution is to select the Retain null values in the source as null values in the data flow option in your flat file source.

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

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 6

Raunak Jhawar (9/20/2010)
NULL can have different meaning when it comes to EXCEL and SSIS
I would advice you to convert all non available fields to some fixed value for instanve say "-1"

I wouldn't do that. Sure, it works most of the time for strings, but certainly not for numeric data types.

If you add a -1 for every null value, it will screw up every aggregate that you make. If it remains null, SQL Server doesn't take them in account when creating aggregates.

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