I'm attempting to import an Excel spreadsheet and needed some guidance on using the LOOKUP transformation. Essentially, I have an Excel spreadsheet with the following columns: Region, FDN, Role, Crop. Within my database, there is a table called Line which these are going to be inserted into. Also, in the database there are lookup tables: REGION, FDN, ROLE, and CROP. In the spreadsheet, there are text values for the columns: REGION FDN ROLE CROP South Y Prod Soybeans West N Test Corn West Y Dev Wheat There are five columns in the LINE table: LINE_ID, REGION_ID, FDN_ID, ROLE_ID, CROP_ID. What I'm attempting to do is instead of inserting the actual text value, I would like to insert th unique ID of the value. So instead of inserting "North" into the table it would insert the corresponding ID (345dsd). Is a LOOKUP transformation the proper method for this? If so, could someone possibly provide an example? Any help would be appreciated! A. M. Robinson

Yes, you would do a Lookup Trnasform for each of the ID Fields You will also need to decide what to do if the lookup can't find a matching row. One approach is to re-direct rows to a Derived Column transform where you assign a value (create a column) that represents the "Unknown" REGION, LINE, etc. Then, UNION ALL the MATCHed output from the Lookup with the Derived Column, matching the Lookup Column of REGION_ID and the Derived Column of REGION_ID. Every element would need these three pieced to handle it.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Thanks for the reply!!! I found an example online, but it's for 2008 and I'm using 2005. Would you know where I can find some examples online? I'm REALLY new to LOOKUP transformations!! Thanks! A. M. Robinson

You could do a lookup or dump the entire Excel file contents into a staging table and then if you know some SQL you can update the table. For now I am providing you with a link to a 2005 Lookup implementation example: http://www.sql-server-performance.com/2009/SSIS-New-Features-in-SQL-Server-2008-Part1/Arthur My Blog

Hi there, Check the below link to a good video on SSIS Lookup Transformation for Excel. I found it useful. http://www.learnitfirst.com/Course/158/Video/2096/Lookup-Transformation-for-Excel.aspx Good luckSharath

Thanks for the reply! Your second suggestion was one I had actually thought of as well. I figure since I'm somewhat pressed for time, I'll need to complete theis task in the manner I'm most familiar with, although I definitely appreciate the heads up on the article. When I have a little more time, I'll definitely delve into it! Thanks again!! AMRA. M. Robinson

Thanks, Sharath! I'll definitely take a look!A. M. Robinson