July 21, 2010 at 7:22 pm
Hi all,
I'm trying to import a '|' delimited flat file to a SQL server 2005 table using a data flow task in SSIS. I've got a flat file source which feeds into a OLE DB destination. The fields I am importing include char(1) columns, varchar columns and numeric columns. My OLE DB destination is configured so that 'Keep Nulls' is true and I'm using the 'Table or view - fast load' Data access mode. What I'm finding is that varchar fields that have no data in the import file are appearing as empty string instead of NULL in the table after import. Also, numeric fields (i.e. numeric(3, 0)), with no data in the import file, are appearing as 0 instead of NULL in the table column. What do I need to do to get NULLS when there is no data for a particular column? By no data I mean the record looks something like
'this field has data||previous field had no data|34||'
So the first, third and fourth columns have data but the second and fifth dont.
In my flat file connection manager I have defined numeric columns with the numeric[DT_NUMERIC] data type and varchar columns with the string[DT_STR] data type.
The columns I am trying to import to are all nullable and have no default values.
My environment is SQL Server 2005 on a 64bit server running windows server 2008. Im using Visual Studio 2005 to develop the package.
July 22, 2010 at 3:46 pm
Did you check the checkbox that "Retain null values from the source as null values in the data flow" in the flat file connection manager?
[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font]
July 22, 2010 at 6:07 pm
That fixes my problem! cheers 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply