SSIS - Performing a Lookup on another Table to get Related Column

Question

I want to executing a select statement in SSIS, but this select statement takes a parameter from another component, and the column of this select statement must be used as inputs to other components.

For example:

select id from myTable where name = (column from a previous component).

And the "id" content of the above select statement should be a column that future components can use.

If i add an "OLE DB Command" component, it allows me to refer to other components as inputs, but I cannot generate an output from it. It seems OLE DB Command component is only used for update/insert statements?

Any ideas on how to do it?

Answer 1

Actually, this is a case for Lookup. It seems you want to do a lookup by name and return id. Pretty simple. Here's how I created an example of this:

1. Drag a Data Flow Task onto the design surface. Double-click it to switch to it.
2. Create a Connection Manager for my database
3. Drag onto the design surface:
   • an OLE DB Source
   • A Lookup Transform
   • An OLE DB Destination
4. Connect the Source to the Lookup to the Destination. It's the "Lookup Match Output" we want going to the destination. See figure 1.
5. Configure the source. My source table just had id and name columns.
6. Configure the lookup
   • General tab: Use an OLE DB Connection
   • Connection tab: specify the same connection, but use the Lookup table. My lookup table was just id and name, but name was made unique, so it makes better sense as a lookup column.
   • On the columns tab, configure name to map to name, with "id" as an output. Configure the lookup operation to be "add new column", and name that column "lookupId". See figure 2.
   • Ignore the other two tabs
7. Configure the output to take all three columns. See figure 3.

That's all. For each row from the source, the name column will be used to match the name column of the lookup table. Each match will contribute its id column as the new lookupId column. All three columns will proceed to the destination.

Figure 1:

Figure 2:

Figure 3: