SSIS - perform a search in another table to get the corresponding column

Question

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

For example:

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

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

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

Any ideas on how to do this?

Answer 1

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

• Drag the data flow task onto the design surface. Double-click it to switch to it.
• Create connection manager for my database
• Drag to design surface:
  • OLE DB Source
  • Substitution Conversion
  • Purpose of OLE DB
• Connect the source to the search at the destination. This is the “Matchup Match Output” that we want to go to our destination. See Figure 1.
• Configure the source. In my source table there were only id and name columns.
• Customize Search
  • General Tab: Use OLE DB Connection
  • "": , Lookup. id , , .
  • , "id" . " " "lookupId". . 2.
• . . 3.

. . lookupId. .

1:

2:

3: