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.