SSIS flat file NULLs loading as literal string 'NULL'

Posted on 2011-04-19
Last Modified: 2012-05-11

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!
Question by:ssebring
    LVL 28

    Accepted Solution

    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

    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    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.
    LVL 21

    Assisted Solution

    by:Alpesh Patel
    At time of data insertion in data flow use Derived column.

    Use condition for 'NULL' string to NULL for all expected columns.

    Author Comment

    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

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now