如何使用SSIS Lookup添加列？关联HR与Employee表引入Probation字段

Hey there, let's walk through exactly how to use SSIS's Lookup Transformation to join your HR and Employee tables on UserID and Date, and pull in that Probation flag to track whether an employee was on probation for each date. Here's a step-by-step guide tailored to your scenario:

• Start by creating a new SSIS package. Then configure OLE DB Connection Managers (or the appropriate connection type for your database) for both the HR and Employee tables, making sure you can successfully connect to and query each table.
• Drag a Data Flow Task onto the Control Flow tab, then double-click it to open the Data Flow design surface.

• On the Data Flow surface, drag an OLE DB Source component. Configure it to point to your Employee table—this will be our primary dataset, and we'll add the Probation field to it.
• Preview the source data to confirm you're seeing UserID, Date, and Actions as expected.

• Drag a Lookup Transformation from the SSIS Toolbox onto the Data Flow surface, then connect the output arrow from your OLE DB Source to the Lookup component.
• Double-click the Lookup component to open its configuration window, and work through each tab:

  General Tab #

  • Choose the Full Cache mode (this is the most efficient for small-to-medium datasets like your example; use Partial/No Cache only if dealing with extremely large HR tables).
  • Select "Use OLE DB connection manager" and pick the connection you set up for the HR table.
  • Choose "Specify table or view" and select the HR table from the dropdown.

  Connections Tab #

  • Set up your matching conditions (these are the keys that link the two tables):
    • Match UserID from the Employee source to UserID from the HR table
    • Match Date from the Employee source to Date from the HR table
    • Ensure both conditions use an "Equals" operator.

  Columns Tab #

  • Here's where we pull in the Probation field:
    • On the right side (HR table columns), find Probation and check the box next to it. You can optionally set an alias (like Probation_Status) to make the output field name clearer.
    • Make sure all your original Employee fields (UserID, Date, Actions) are included in the output (they should be checked by default).

  Error Output Tab #

  • Decide how to handle rows that don't find a match in the HR table (your sample data has perfect matches, but real-world scenarios might not):
    • Ignore failure rows: Skip any Employee rows that don't have a corresponding HR entry
    • Redirect rows: Send unmatched rows to an error output for later review
    • Fail component: Stop the package if any unmatched rows are found (not ideal for production)

• If you want to save the combined dataset, drag an OLE DB Destination onto the Data Flow surface and connect the Lookup component's output arrow to it.
• Configure the destination to write to a new or existing table (e.g., Employee_With_Probation), then map the input fields (UserID, Date, Actions, Probation) to the destination table columns.

• Save your package, then run the Data Flow Task.
• After execution completes, check your destination table—you should see the combined data exactly as expected:

  UserID	Date	Actions	Probation
  552	01-01-2018	234	1
  552	01-02-2018	222	1
  552	01-03-2018	109	0
  552	01-04-2018	267	0

内容的提问来源于stack exchange，提问作者Jess8766