How do I retain NULL values when using SSIS to import from flat file in SQL Server 2005

I've exported records to a flat file delimited by "|" and it seems that when I import those records into a new database , SQL Server treats the NULL values as empty fields. IMy queries worked 开发者_如何学运维properly when the records/fields were NULL and so I want to either find a way to retain the NULL values in the data or convert the blank fields to NULL values. I'm assuming the former would be easier, but I don't know how to do that. Any help would be appreciated.

I just had the same problem. I resolved it by Changing the RetainNulls property in the properties of the Flat File Source in the Data Flow Task.

In your destination connection in the dataflow, there is a property that you can chceck that says Keep nulls, JUst check that. Why that isn't the default I'll never know.

Hmmm something stange going on there. I can suggest that you then clean the data and change it to null, you can either do this as part of the dataflow or do two dataflows, one which inserts the data into a staging table, then run an exectue SQl task to do the clean up and then create a dataflow to run fromthe staging table to the real table.

in case anyone is looking how to do this when building the package programatically you need to set the variable in your CManagedComponentWrapper object

CManagedComponentWrapper instanceSource = ComponentSource
...
instanceSource.SetComponentProperty("RetainNulls", true);