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.

Read three tables to fill one destination table

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.

Result of this task

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.

1 Answers

vish On

You can use the three point object name (i.e. DB.Schema.Table) and refer to objects in multiple databases in your single query.