Insert Data From One Table to Another Table Using SSIS

Look at given below points:

Create a sample flat file named Source.csv.
In the SQL database, create two tables named dbo.Category and dbo.Product.
On the package, place an OLE DB connection to connect to the SQL Server and Flat File connection to read the source file. Configure the flat file connection.
On the Control Flow tab, place two Data Flow.
Inside the data flow task named Category, place a Flat File source and ssort transformation and an OLE DB.
Configure the flat file source. We need to read the flat file source.
Configure the sort transformation. We need to eliminate the duplicate values so that only the unique records are inserted into the Category table dbo.Category.
Configure the ole db destination. We need to insert the data into the Category table dbo.Category.
Inside the data flow task named Product, place a Flat File source, Lookup transformation and an OLE DB destination.
Configure the flat file source. This configuration is same as the flat file source in the previous data flow task.
Configure the lookup transformation. We need to find the Category id from the table dbo.Category using the other key columns present in the file. The key columns here are the CategoryName. If the file happened to have a unique column, you could just use that column alone to fetch the Category id.
Configure the ole db. We need to insert the Category id into the table dbo.Product.