SSIS flat file NULLs loading as literal string 'NULL'


I've already poured over blogs trying suggestions -- hoping someone has additional ideas here.

1. NULL in a txt file is loading into sql server table as literal string 'NULL'
2. '' (blank) is being loaded as NULL

Database settings:
   ANSI NULL Default      False
   ANSI NULLS Enabled      False

No defaults, rules or triggers are defined on table

table def:
   ItemCode varchar(50)
   ItemColorCode varchar(30) null
   ItemDesciption varchar(50) null

source: unix binary text file
gzipped on unix using plink
ftp'd from unix to windows machine using ssis script task
gunzip.exe file (on windows machine)
inserted into sql server table using:

Flat File Connection Manager
      Pipe Delimited / Code page: 1252 (ANSI - Latin 1)
      Preview shows blanks and nulls where expected

Text file contains (final insert row should be exact):

ItemCode   ItemColorCode       ItemDescription
PRE BILL                                              NULL

(where ItemColorCode is blank, ItemDescription is null)

Inserts into SQL Server table as:

ItemCode   ItemColorCode      ItemDescription
PRE BILL      NULL                     NULL  --literal 'NULL'

(where ItemColorCode is null, ItemDescription = 'NULL')

Flat File Data Source
     checked "Retain null values from the source as null values in the data flow"

OLE DB Destination Editor
     tried both checking and unchecking 'Keep Nulls' with same results:

ItemCodeCode = NULL
ItemDescription = 'NULL'

Of course adding a Derived Column Task with:

ItemDescription == "NULL" ? (DT_STR,50,1252)NULL(DT_STR,50,1252) : (DT_STR,50,1252)ItemDescription

works.  Hoping for suggestions on how to resolve this without the Derived Column task.

Many thanks!
Who is Participating?
Ryan McCauleyData and Analytics ManagerCommented:
I'm not sure how you'd do on the way through - I'd probably end up with a derived column, as you have. I'd prefer that to the other alternative, which is a SQL Task at the end that updates the column using SET Description=NULL where Description='NULL'

I'd stick with the derived column - you could do it for any (or every) column with a case statement for all the ones you want to check/change:

CASE WHEN description='NULL' THEN NULL ELSE description END

Open in new window

Guy Hengel [angelIII / a3]Billing EngineerCommented:
the problem is that if the file contains NULL (string), you have to "translate" that into the db NULL value. the derived columns seems to be the best, as it will avoid to update the table after the fact.
Alpesh PatelAssistant ConsultantCommented:
At time of data insertion in data flow use Derived column.

Use condition for 'NULL' string to NULL for all expected columns.
ssebringAuthor Commented:
thank you for all of your comments - was hoping there was some hidden trick without the additional task step (perhaps in 2008) but wishes only go so far... :) cheers
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.