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 11 (of 11 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: 247180 More actions · Answer 2

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

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: 247180 More actions · Answer 4

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

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: 247180 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.

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: 247180 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?

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: 247180 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?