Keep Null option in oledb destination in SSIS

This option is enabled when we are selecting Data access mode to “Table or view – fast load”

  

By default this setting is unchecked which means default value will be inserted (if the default constraint is defined on the target column) during insert into the destination table if NULL value is coming from the source for that particular column. If we check this option then default constraint on the destination table's column will be ignored and preserved NULL of the source column will be inserted into the destination.

Let’s see the example

Here I am creating a table with the default value

create table student ( ID int Identity(1,1), Fname varchar(50), Lname varchar(50), SSubject varchar(50), Saddress varchar(100), Mob VARCHAR(10) default 'NA' )

Mob is default value ‘NA’.

We have to load from the table. Now I am taking Data flow task

  

Taking source as OLEDB and creating the connection string.

See in three records I have taken mobile number as null.

Now I am taking OLEDB destination and creating the connection. Selecting Data access Mode as Table or view – fast load.

  

Here I am keeping nulls as unchecked. In this case when we will load this data into the date then if we are getting data from the file in this case null value we default value inserted.

Before running the package records in the table.

Running this package.

See the records in the table.

   

It means when we unchecked the keep null then default value will be inserted (if the default constraint is defined on the target column) during insert into the destination table if NULL value is coming from the source for that particular column.

When we mark as checked

  

Before running the package records in the table

  

Now running the package

See the records in the table.

If we check this option then default constraint on the destination table's column will be ignored and preserved NULL of the source column will be inserted into the destination.

Hope this help to understand the concept of keep null option in oledb destination.