Okay, So, here is the deal. I am taking a flat text file, cleaning it up, and putting it into a SQL Table in an SSIS package. This table includes employee numbers. I need to then connect to a different server, database, and table to then look up the employees email and their supervisor employee number based on their employee number (this email can change and the company only wants one master copy of this employee data table, so taking a copy of this table is not an option). I will then need to take what I got with the supervisor number and look up their email as well. These two emails will then need to be sent a single structured email. Is this possible without taking a local copy of the email address? This will need to be done for 500+ employee numbers a month. I cannot seem to wrap my head around this one.
Start with both tables as source components and do a Merge Join on employee number to match the rows from the two tables.
Same thing to match these records with a third source component to get the supervisor emails.