SSIS export null value

Question

Post reply

SSIS export null value

Saritha-450752

Say Hey Kid

Points: 690
More actions
February 2, 2010 at 3:39 pm

#156637

On exporting the query results in dataflow task from the SQL server 2005 database, the null values are exported as blanks in to the flat file. But i want to retain the same null values.

Viewing 11 posts - 1 through 10 (of 10 total)

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

emily-1119612 SSCarpal Tunnel Points: 4166 More actions · Answer 1

>>But i want to retain the same null values.

You question seems to indicate the following article may be helpful.

http://en.wikipedia.org/wiki/Null_(SQL)

I am going to guess and say you want the phrase 'NULL' to appear in your export.

Use COALESCE

http://msdn.microsoft.com/en-us/library/ms190349.aspx

SELECT COALESCE(YourField,'NULL')...

If you field is NULL you then will return the string 'NULL'

Phil Parkin SSC Guru Points: 245813 More actions · Answer 2

Please show an actual example of the data in your flat file which shows how 'blank' and 'null' differ.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Saritha-450752 Say Hey Kid Points: 690 More actions · Answer 3

Here is the sample data

Sample Value where null is replaced

DATABASE VALUES- Affinity,NULL,CT

EXPORTED VALUES IN FLAT FILE(blank) - Affinity,,CT

Phil Parkin SSC Guru Points: 245813 More actions · Answer 4

,, in a text file is the equivalent of NULL in a database table.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Saritha-450752 Say Hey Kid Points: 690 More actions · Answer 5

Thanks emily but still 'NULL' and NULL makes a difference when you import in the table.

Phil Parkin SSC Guru Points: 245813 More actions · Answer 6

You use the word 'retain' in your original post. If a value is null in a database column and you export it to a text file, the way of retaining that value is to export it as ,,

anything else is changing it, not retaining it.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Saritha-450752 Say Hey Kid Points: 690 More actions · Answer 7

Thank you phil but ,, when imported again into the database is inserted as Space and not null so that makes a difference. As we are feeding into other applications, we cannot change the importing code. Try this by exporting and reimporting.

Phil Parkin SSC Guru Points: 245813 More actions · Answer 8

Something else is happening here.

If I import the record

Affinity,,CT

into a database, I can guarantee that field number 2 would not somehow become a space in my database table. Do you really mean a space (one character), or just an empty string?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Saritha-450752 Say Hey Kid Points: 690 More actions · Answer 9

Saritha-450752

Say Hey Kid

Points: 690

February 4, 2010 at 12:47 pm

#1113943

It is empty string

Phil Parkin SSC Guru Points: 245813 More actions · Answer 10

I just tried it and it worked as I suggested.

Against the properties for your flat file source, have you got 'retain nulls' set to true?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.