A question about distributed databases. After a migration to SQL2016, we used a DWH project, the Staging / Extraction DB on a separate server. DW + DM is also located on a server. The dtsx packages are also on the DWH / DM server (package deployment).
In some dataflows, there is an SQL join with full qualification.
SELECT c1, c2
FROM [DWH]. [Dbo]. [TableA] AS a,
JOIN [staging]. [Dbo]. [TableB] as b ....
The problem: The table from DB "DWH" is local. The table from DB "Staging" on another server.
How can I access this join in SSIS-DTSX (DFT, Lookups, SQL, etc.)?