Keep Identity 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 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.