SSIS: From Excel to Sql server table with IDENTITY column

Recently I came across one of the question in a popular forum about inserting data from excel to SQL Table with Identity column values of SQL Table coming from Excel. This is very much possible that you have a SQL table with an Identity Column and you want to insert data from Excel file and point of interest is values for Identity column of SQL Table are coming from the excel file. Let us assume we have a SQL Table with following structure:

The ID column is an Identity column. The SQL nature is every time new row will be inserted in this table, the Identity column will populate automatically with the next incremented value. But in a scenario when the ID column values are coming from Excel File – What should be the behavior? Let us assume we have a excel file and we have data like this.

We want to store ID column values of Excel sheet in the SQL table “Excel_ID” and want to store it in ID column of SQL table. With SSIS this is pretty simple. All we have to do is create a Package and add a Data Flow Task into it. We need two connections here. One Excel connection to point to our excel file and second OLE DB Connection to point to our SQL Server and SQL table. Under Data Flow Tab we have following structure

In the OLE DB Destination component we need to check the Keep Identity checkbox.

This will ensure that the Identity check is disabled at the SQL Server table and whatever values are coming from Excel file is stored in the Identity column. After running the package we found that all the values from Excel Column ID have been successfully inserted in the SQL table.

The same thing can be achieved using Execute SQL Task with SSIS.