Find RouteID from RouteName

I have Excel Input and SQL Server Output

The Excel provides a RouteName, I need to look up the RouteName in another SQL Table to find the RouteID, then output the RouteID.

I thought maybe Lookup Data Flow Transformation would do the trick, but this only looks up whether the row exists. Can anyone advise what Transformation is needed to convert RouteName into RouteID by looking up in a SQL Table?

Thanks

Richard

The lookup transform will also return a value from the table. In your scenario you should select the ID and Name from the SQL table in the first tab of the lookup. On the second tab, connect the name from the input (your excel spreadsheet) to the name on the lookup (the SQL table). Then check the ID column on the lookup. The ID column will be added to the data flow. When the data flow is executed, if the lookup finds a match on name, the ID column will be populated with the appropriate value.|||Thanks. That did the trick!