[SOLVED] Metadata for multisource in SSIS

Metadata for multisource in SSIS

I have 2 sources A and B. Each source has 10 identical tables. Now I want to ETL with SSIS using Incremental Update method. So I need to use 1 metadata to contain LSET and CET values. But when I decide to insert values into metadata, I have a question that what data should I put in?

Is it the LSET and CET values of each source, or is it the LSET CET of each table of each source?

If it is the first one, it will only be 2 rows, but with the latter it will be 20 rows. I wonder what is the way people usually use?

Method 1:

INSERT INTO data_flow (name, LSET, CET)
VALUES 
    ('A', '1/1/2020', '1/1/2020'),
    ('B', '1/1/2020', '1/1/2020');
GO

Method 2:

INSERT INTO data_flow (name,source, LSET, CET)
VALUES 
    ('Table1','A', '1/1/2020', '1/1/2020'),
    ('Table1','B', '1/1/2020', '1/1/2020'),
    ('Table2','A', '1/1/2020', '1/1/2020'),
    ('Table2','B', '1/1/2020', '1/1/2020'),
    ....
    ('Table2','B', '1/1/2020', '1/1/2020');
GO

Solution

With 2 sources you should track LSET and CET per table and per source.

With Method 1 (single LSET and CET for each source):

Tables without changes during LSET window could miss updates.
When only one table requires updates, processing all is inefficient.
Difficult to isolate any errors that might occur.

The small increase in metadata records in Method 2 is a small price.