I am trying to design a part of an SSIS package (Data Flow Task) that uses an excel spreadsheet for its input and output. I am good on the source/destination setups.
I want to take one column from the input spreadsheet (userid) and use that in a query that returns the corresponding employee id from a database, replacing the userid with employee id in the output spreadsheet.
Can you advise me on the appropriate data transformations to use for this?
I am using the BI studio for SQL Server 2005.