sql server 2000 DTS by wrox is a pretty good source for this. they really go into detail on how to leverage the features.
if you dont want to read this book, here are some considerations:
1) if you are moving the data from file to table and using lookup tables, it sounds like you have more than one table.
2) The way to think about this is think of it as a challenge to automate the normalization of a denormalized table.
2) TableA: start by importing the raw data into TableA - it is denormalized. (use the data transform process).
3) TableB: create a table that is almost identical, except, where you need to have foreign key references. Change the datatype and\or name to reflect the values stored in the PK.
4) use a stored procedure to insert from TableA to TableB. where the value must be changed to the fK reference, use a function that will do a quick lookup. if a value is not present in the lookup table, write code that will put the NEW value in an exception table and\or email you (xp_sendmail).
Get Text File => Import Data to Denormalized Table (TableA) => Load to Normalized Table (TableB)
insert into dbo.TableB
select firstname,lastname,fnConvertEmployeeStatus(Employe eStatus)
Example Function (semi-psuedo code):
set @EmployeeStatusID=(select EmployeeStatusID from EmployeeStatus where EmployeeStatus=@EmployeeStatus)
if @EmployeeStatusID is null
send mail warning to dba
insert new value into exception table
return 1 'default value