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.
Destination Table Definition:
CREATE TABLE dbo.Customer(
ID INT IDENTITY(1,1),
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).
Drag Flat File Source and create connection to text file as shown in Fig 2 and Fig 3.
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.
Map the Source columns to the destination table columns.
Fig 5- OLE DB Destination Mapping
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.
As we can see that all the records with source IDs are inserted successfully.