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.
