Tuesday 18 December 2007

Retain Nulls

This slowed me down for a few minutes today - so might be useful (well fairly anyway).

Importing a text file using SSIS, and writing to a table in a Data Flow Task. Problem was that empty date fields in the text source were causing '1753-01-01 00:00:00.000' to be written to the table - we obviously wanted NULLs. The solution was to right-click on the Flat File Source -> click 'Show Advanced Editor...' click on the 'Component Properties' tab and then set the 'RetainNulls' property to 'True'. Obvious really but I'll add a picture just in case you didn't follow:


The code for doing this programmatically you say? Why here it is:

Dim flatFileSource As IDTSComponentMetaData90 = flow.ComponentMetaDataCollection.New()
Dim srcInstance As CManagedComponentWrapper = flatFileSource.Instantiate()

srcInstance.SetComponentProperty("RetainNulls", True)

Bob's yer Uncle - NULLs instead of 1753.

Don't forget that the Flat File Task and associated Flat File Data Source component need to have date fields configured to the DT_DBTIMESTAMP data type.

19 comments:

KenJ said...

Fairly useful, as asserted.

Thanks!

Layne said...

Thank you! Just what I needed.

Lara Rasner said...

Saved me from pulling ALL my hair out, thanks a MILLION!

Unknown said...

Hi i am using SQL 2005, and i am trying to use the import wizard to import a CSV.

i am getting errors where smalldatetime has empty fields

How do i access the Advanced editor for flat file screen as per ure document?

Any advise would be very welcome

Phil said...

Ronak,

First I would suggest not using the wizard - it really isn't saving you that much work, as you have discovered.

However, if you still want to use the wizard and assuming the destination is a table which allows NULLs I would do the following. Create a dummy file without empty fields and run it through the wizard. Then you should be able to edit the package as my blog entry suggests. Then point the package file connector to your original file, with the empty fields and run the package again.

Unknown said...

Thanks Phil,

The trouble is that my expertise are limited so i figured import wizard would help.

What other method would you suggest?

i am importing tables containing anywhere from 2 rows to 2 million rows

if i can do this without using integration services it would also save me a lot of hassle.

Appreciate your kind help nonetheless

Thanks

NC said...

Thanks a lot!

Phil Brammer said...

Don't use the Advanced Properties, simply double click on the Flat File Source component and check the box to "Retain null values from the source as null values in the data flow."

Paul Kohler said...

Thanks Phil, that hit the spot!

Skywarpgold said...

omg, this is exactly what was tripping me up. Thanks so much!!!

Anonymous said...

This was very useful to me.Thank you very much.

Anonymous said...

Thanks. This solved a problem I was having with destination fields that are SMALLDATETIME.

Anonymous said...

Very helpful! It took an hour of searching to find the solution, but your blog was the golden key!

Nick said...

Tearing hair out
found your blog
Won't buy wig.

Anonymous said...

Super!! Your clue (Retain Nulls) = True put an end to 2 days struggle!!!!

Aravind R.Patlolla said...

aah.. helped me when i thought i'm doomed. thanks a lot.

Anonymous said...

Good post. Very helpful

Anonymous said...

Very helpful

Anonymous said...

Thank you so much..after a long struggle your post solved my issue.