SSIS - How To Insert Data Into a SQL Server Table with Identity Column

Scenario:

We have dbo.Customer table in our SQL Server database.The ID column is identity column. We need to load the data from Flat File Source but want to insert the values in ID column from the source instead of SSIS create identity values for ID column.

Fig 1. Flat File Source with Identity Values

Destination Table Definition:

CREATE TABLE dbo.Customer(

ID INT IDENTITY(1,1),

Name VARCHAR(50),

ADDRESS VARCHAR(50),

Phone VARCHAR(12))

Solution:

We will be reading data from text file by using Flat File Source and then load the data into OLE DB Destination ( SQL Server Table).

Step 1:
Drag Flat File Source and create connection to text file as shown in Fig 2 and Fig 3.

 Fig 2- Create Flat File Connection in SQL Server Integration Services (SSIS)

Fig 3-Flat File Connection-Preview Data 

Step 2:
Drag OLE DB Destination to Data Flow Pane and connect Flat File Source to it.Configure as shown below. Check the check Box for Keep Identity as shown in #4.

Fig 4- Configure OLE DB Destination

Map the Source columns to the destination table columns.

Fig 5- OLE DB Destination Mapping


Final Output:
I have insert one more record  with ID=5 in our source file. Let's run our SQL Server Integration Services Package and check if all the records are inserted correctly.

 Fig 6- View records in dbo.Customer table 

As we can see that all the records with source IDs are inserted successfully.