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

Question

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?

Unsparing

See my answer here please The wizard does not expose the correct seting you need... – Penetrance

Does this mean there is no way within the Import/Export wizard to switch on the 'keep nulls' setting? Of the alternatives, which is the most efficient way to import the data? Thus far, I have only used the wizard to import. Apologies if this seems like a basic question - I am not a programmer, my job is to analyse the data and produce reports based on it, but in order to do that I need to get it into the database. – Unsparing

that is correct, sorry. Other ways to import data are more for "programmers" – Penetrance

Answer 1

@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.

Unsparing