Retaining NULLs in numerical columns using SSIS Import/Export Wizard?

sql sql-server sql-server-2005 ssis flat-file
Lethanta picture Lethanta · Feb 24, 2010 · Viewed 8.3k times · Source

I am having a problem uploading data from tab-delimited flat files (TSV files) into SQL Server 2005 using the SSIS Data Import wizard. I did not experience this problem using the equivalent procedure in SQL Server 2000, and I have checked that the internal structure of the files I am trying to import is unchanged since well before the SQL Server upgrade took place.

The problem is that all blank values in columns with numeric data types (e.g. smallint, float etc) are being converted to 0s on import, instead of NULL. This means that AVGing across these data is giving erroneous output.

The TSV files do not include text qualifiers, however testing the use of qualifiers with some dummy data did not lead to a resolution of this problem.

It is possible to retain the NULLs by importing into VARCHAR columns, however this is far from ideal. Is there a way of instructing the SSIS Import/Export wizard to import blank values from flat files into columns with numeric data types as NULL rather than 0?

Answer

Lethanta picture Lethanta · Feb 25, 2010

@gbn: Thanks for the pointer. I believe I have now found a way around this problem and have been able to successfully import into my SQL Server 2005 database data containing NULL values in numerical columns.

In case anyone else is having the same problem:

I imported the data using the Data Flow task in the Business Intelligence Development Studio (rather than using the dtswizard as previously) by building a Data Flow task from Flat File Source to OLE DB Destination.

In the Flat File Source Editor box there is a 'retain null values from the source as null values in the data flow' tick-box. Ticking this appears to resolve this problem.

As @gbn pointed out, this box is missing from the wizard.

Related questions

SQL Server Agent Jobs: How to execute a job step without executing the entire job

I have a SQL Server Agent Job that previously had two steps. Today, I've had to integrate a third step and soon I'll need to integrate a fourth. I want to be sure that the step will execute properly but …

Read More
Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More