How to use the master table as a lookup to populate the foreign key in the Reference table during SSIS data load

Question

if i have an excel file with data in the following columns/format

• DeptName
• DeptLocation
• Description

I also have an SQL table for Employees with the following columns

• EmployeeId
• DeptId(foreign key)
• DeptLocation
• Description

And then another SQL reference Departments table with following columns

• DeptId(Primary key)
• DeptName

Departments table is already populated with master data. I now want to populated Employees table with the data in the excel file using SSIS Dataflow task, an excel source and an OLE DB Destination.

Given the DeptName in the Excel file, how can i lookup its corresponding DeptId from the Departments table and have it set as the DeptId foreign key value in table Employees.

Basically, i want to get the DeptName in the excel file, lookup its corresponding DeptId value from the Department Table and set it to DeptId(foreign key) in Employees table during data load.

Answer 1

1. Merge Join between Excel Source and the Departments table data (join on Department Name) gives DepartmentId for each Department Name. Make sure to trim and use same case when joining both sources.

2. Use the output of the above merge join to join with Employee Table(join on Dept ID column).