We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

SSIS flat file NULLs loading as literal string 'NULL'

Medium Priority
2,319 Views
Last Modified: 2012-05-11
Hi,

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

Issue:
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
etc.

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

Many thanks!
Comment
Watch Question

Senior Data Architect
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.