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 the destination table (if it has
an identity column) will create identity values on its own. If we check this
setting, the dataflow engine will ensure that the source identity values are
preserved and same value is inserted into the destination table.
Let’s see the example
Here I
am creating a table with Identity column value.
|
create table student
(
ID int Identity(1,1),
Fname varchar(50),
Lname varchar(50),
SSubject varchar(50),
Saddress varchar(100),
Mob VARCHAR(10)
)
|
Below file, we will load into the table
Now I am taking data flow task
Taking source as a flat file and creating
configuration.
Taking oledb destination
Keeping
Identity as unchecked
Now
running the package
Before
run, records in the table
Now
running this package
See
the record in the table
Identity the column is created when the data is loaded.
Now
I am checked the keep identity option
Now
running the package
Ooooooooooooooooooooooo
it failed.
[OLE DB Destination [31]] Error: SSIS
Error Code DTS_E_OLEDBERROR. An OLE DB
error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native
Client 11.0" Hresult:
0x80004005 Description: "The
statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Server Native
Client 11.0" Hresult:
0x80004005 Description: "Cannot insert
the value NULL into column 'ID', table 'Test.dbo.student'; column does not
allow nulls. INSERT fails.".
Now I am adding ID column in the
source file.
Mapping the ID column with the destination.
Now the package is ready to run.
Before run record in the table.
Running this package.
See the records in the table
Basically, if we marked it checked then
in the table the identity column is ignored. See the example
After execution of the package,
records in the table.
Hope this will help to understand the
keep Identity option in OLEDB destination.
