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:
Fairly useful, as asserted.
Thanks!
Thank you! Just what I needed.
Saved me from pulling ALL my hair out, thanks a MILLION!
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
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.
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
Thanks a lot!
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."
Thanks Phil, that hit the spot!
omg, this is exactly what was tripping me up. Thanks so much!!!
This was very useful to me.Thank you very much.
Thanks. This solved a problem I was having with destination fields that are SMALLDATETIME.
Very helpful! It took an hour of searching to find the solution, but your blog was the golden key!
Tearing hair out
found your blog
Won't buy wig.
Super!! Your clue (Retain Nulls) = True put an end to 2 days struggle!!!!
aah.. helped me when i thought i'm doomed. thanks a lot.
Good post. Very helpful
Very helpful
Thank you so much..after a long struggle your post solved my issue.
Post a Comment