SSIS Using column name in Excel to do a Lookup

I have an Excel sheet that has data with column headers (names). These column names that are in Excel exist as rows in one of my tables in the DB. What I need to do is to do a lookup on these Excel column names against the table in my 开发者_开发问答DB to retrieve the id value. How do I do that?

Thanks for your help.

You could pull out columns in Excel to variables in SSIS. Then you could use the values in variables to get data from DB. Also, by using for-each loop, you could set fixed number of columns each time if you have too many column names.

I know this is a very old question and I just stumbled on this. We faced a similar situation and we used a cache transformation to get around. You can dump the excel data to a cache (using a cache transformation) and then on the Lookup, you can lookup on this cache rather than an OLEDB table. We have done this multiple times with no problems.