SSIS Automatically Coverting a NULL to a Blank Space

Question

Post reply

SSIS Automatically Coverting a NULL to a Blank Space

cpiacente

Old Hand

Points: 341
More actions
May 13, 2025 at 11:41 am

#4590591

Hello,
I was wondering if anyone knew how to prevent this issue I am experiencing. I have a SQL Source with a query that is just selecting data where fields are casted as a varchar value. When running the query in SQL Server, the values either have a valid value or are null as expected. However, when previewing or running it in SSIS, the NULL sometimes will automatically change to an empty string or blank. I cannot for the life of me understand why this is. Any ideas?
Thank you in advance!

Viewing 8 posts - 1 through 8 (of 8 total)

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

Phil Parkin SSC Guru Points: 247159 More actions · Answer 1

Phil Parkin

SSC Guru

Points: 247159

May 13, 2025 at 11:45 am

#4590592

What is the target? SQL Server, Excel, CSV, ...

cpiacente Old Hand Points: 341 More actions · Answer 2

The target is also SQL Server. This occurs when preview the source as well.

Phil Parkin SSC Guru Points: 247159 More actions · Answer 3

OK. The source is SQL Server and it's a simple query?

SELECT col = cast(x as VARCHAR(50)) FROM tab1

sort of thing?

cpiacente Old Hand Points: 341 More actions · Answer 4

yeah select cast(col as varchar(128)) as column_name in this case.

Phil Parkin SSC Guru Points: 247159 More actions · Answer 5

I really don't know. But for a quick fix, you could try

select NULLIF(cast(col as varchar(128)),'') as column_name

cpiacente Old Hand Points: 341 More actions · Answer 6

Yeah I did know we can do that but was hoping to understand more about why it is happening because some records are null and some are the blank space but can't for the life of me understand why.

Phil Parkin SSC Guru Points: 247159 More actions · Answer 7

cpiacente wrote:

Yeah I did know we can do that but was hoping to understand more about why it is happening because some records are null and some are the blank space but can't for the life of me understand why.

There's nothing obvious that anyone here will be able to help you with, unless you can provide more information.

For example, if you post some sample source data, in the form of create table / insert statements, which exhibits the problem, someone here will be able to test for themselves whether the same issue happens to them.