Menu
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.
475
You can use the derived column task. Place between your source and destination. Use it to replace the string null with a null value.
Solution: We will be using Derived Column Transformation in our SSIS Package to replace these blank values with Null. Drag Derived Column Transformation to Data Flow Pane and connect the Flat File Source to it. Add a new column DER_Address and write expression as shown below in fig 2.
If the “Keep nulls” checkbox is checked, the default constraint on the target table is not used – thereby maintaining the same data as the source.
1. Create a new SSIS package and a new Data Flow Task within the package. 2. Drag the Flat File Source adapter from the Toolbox onto the Data Flow workspace and then double-click the Flat File Source to open the Flat File Source Editor.
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.
193
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.
16
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
