Below are my three source tables.

1. CAPTION (Master Table) COLUMNS: CaptionId INT PK CaptionText nvarchar(50)

Example Values: Record #1: 1, Road Accident Record #2: 2, Safety Criteria

1. BATCHDATES (Master Table) COLUMNS: BatchDateId INT PK BatchDate DATETIME

Example Values: Record #1: 1, 2019-01-31 00:00:00.000 Record #1: 2, 2019-02-28 00:00:00.000

3. GENERATED SQL STATEMENT This one has sql statement with aggregated rows.

Hence, Above three are sources to read record and below one is where I am trying to write data.

1. FleetTable (Destination Table) COLUMNS: ID INT PRIMARY KEY, **CaptionId INT,**BatchDateId INT, FleetType nvarchar(10), OperatingStatus nvarchar(25), FleetRange_1 numeric(10,3), FleetRange_2_4 numeric(10,3)

NOTE: Last 4 columns in above table are derived from SQL statement from point #3.

So far I have tried below using SSIS with no avail.

Honestly, I cannot really share any code but an attached image above as I am intending to use SSIS.

As an expected result, I am intending to see output like below.

Some good insight from community is appreciated.

MORE DESCRIPTION: In above point #3 3. GENERATED SQL STATEMENT, I am deriving a query which belongs to completely separate database and it has no reference to above master tables. And tables in first two points belong to separate database. Hence, I am not sure how lookup would help.