SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Retain NULL values vs Keep NULLs in SSIS Dataflows - Which To Use?

By Paul Ibison,

There is some confusion as to what the various NULL settings all do in SSIS. In fact in one team where I worked we had created 15 packages before realising the full implications of the various default settings. Anyway, hopefully this article will help to clarify a bit......

Suppose you are doing a bog-standard import of a text file like the one below. We have a data flow task with a flat-file source going to a OLEDB destination. The flat file contains 3 columns: MyDate, MyInt and MyText. In the file there are 2 rows - one containing normal values and the other one just containing blank column values. So, what happens in the case of a blank value?

Data flow

By default, what will happen is shown in the first dataset below (2nd row). A blank date will become "1st Jan 1753", an integer will become "0" and text will become "". Is this what you want? Usually not - especially if "0" is also a legitimate explicit numerical value in the text file!

Imported Data

So, we can designate that the null values from the flat file (really an absence of a value) will become a null in the resulting OLEDB destination. There is a little checkbox on the flat file source which controls this:


The imported dataset will now look like this:


So far so good.

However there is another similar setting available here. This is a checkbox on the OLEDB destination labelled as "Keep NULLs" - what is that for?

Basically this controls what happens when you have defaults on the destination table. If there is a default constraint on each column –


the date should be today's date,

the number should be -1

the text "n/a"

- do we want these default values to be overwritten by NULL values or to simply apply their specified defaults?

The relevant option is shown below. In this particluar case the defaults won't get applied and will be overwritten by NULLs from the source file.


If it was unchecked, and the defaults mentioned above existed, we would arrive at the values below.

Imported Data

Hopefully this makes these 2 setting and the various permutations s a little clearer Happy Face Paul Ibison, http://ssisblog.replicationanswers.com

Total article views: 4409 | Views in the last 30 days: 6
Related Articles

Automate Default Constraints

One procedure that will take care of all default constraint values in the destination.


Server becomes very slow [RESOURCE_SEMAPHORE] waittype.

Server becomes very slow [RESOURCE_SEMAPHORE] waittype.


SQL server become unresponsive

SQL server become very very slow


DBCC SHRINKFILE shrinks below specified file size

Initial size vs. default size


Dynamic Excel destination

Dynamic Excel destination