SSIS Series: Use of the Lookup component and several of its caching patterns

Let's simply show you how to implement a simple example using the Lookup component-export data from table A in data source to target datasheet B, insert new data into B if a data does not exist in B, and maintain a uniform update B and A table data if it exists.

Then explain some of the terms used in this process, and analyze several caching patterns, their characteristics, and common situations that appear in Lookup.

Two tables, one is the target table Demo_lk_customer, one is the demo_lk_legacycustomer old system table. We can understand that this example is going to achieve the goal of Demo_lk_customer table data to be synchronized with the Demo_lk_legacycustomer implementation, consistent.

Use biwork_ssis Go--The Look Up demo table IF object_id (' Demo_lk_customer ', ' U ') are not NULL DROP table Demo_lk_custome R go IF object_id (' Demo_lk_legacycustomer ', ' U ') isn't NULL DROP TABLE demo_lk_legacycustomer go CREATE table
    Demo_lk_customer (CustomerID INT PRIMARY KEY, Customercompany NVARCHAR (255), CustomerName NVARCHAR (20), CustomerAddress NVARCHAR (255)) CREATE TABLE demo_lk_legacycustomer (CustomerID INT PRIMARY KEY, Customerc
    
Ompany NVARCHAR (255), ContactName NVARCHAR (m), ContactTitle NVARCHAR (+), customeraddress NVARCHAR (255))  INSERT into Demo_lk_customer VALUES (1, ' hfbzg ', ' allen,michael ', ' obere Str. 0123 '), (2, ' Mltdn ', ' Hassall, Mark ', ' Avda. de La Constitución 5678 '), (3, ' kbude ', ' Peoples, John ', ' Mataderos 1000 ') INSERT into Demo_lk_legacycustomer VALUES (1, ' Nrzbb ', ' allen,michael ', ' Sales Representative ', ' Obere Str. 0123 '), (2, ' Mltdn ', ' Hassall, Mark ', ' Owner ', ' Avda. de la Constitución 5678 '), (3, ' kbude '),' Peoples, John ', ' Owner ', ' Mataderos 7890 '), (4, ' Hfbzg ', ' Arndt, Torsten ', ' Sales Representative ', ' 7890 Hanover Sq. '), (5, ' Hgvlz ', ' Higginbotham, Tom ', ' Order Administrator ', ' Berguvsvgen 5678 '] SELECT * FROM Demo_lk_customer select * from D Emo_lk_legacycustomer--update demo_lk_customer SET CustomerName =?, Customercompany =?, CustomerAddress =?
WHERE CustomerID =? --update demo_lk_customer SET CustomerName =?
WHERE CustomerID =? --update demo_lk_customer SET customeraddress =? WHERE CustomerID =?

In the test data, we think the IDs of both tables are unique, the 1th and 3rd data are inconsistent, and the 4th and 5th data do not exist in the target table.

Take a look at the SSIS Package architecture that implements this example, and the outermost is a data flow df_lookup.

In a data stream, the data source Ole_src_legacycustomer uses SQL Server database tables in this example, but the data source can also be a text file, a table in Excel or another database, or a result set for a query.

This data source is an input to the lookup component relative to the lookup component Lkp_customer.

After Lkp_customer there are two branches-matching and mismatched branches, doing things that match the data to do the update, mismatched data to do the insert action.