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.
Example:
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.)?
I expect that I am answering the wrong question, but you can use 3-part and 4-part naming (dunno specifically in SSIS if that is possible, as I don't use that).
FROM [localDatabase].dbo.[LocalTable]
FROM [RemoteServer].[RemoteDatabase].dbo.[RemoteTable]
We NEVER use 3-part and 4-part naming but, instead, create a SYNONYM. That means if the darn thing changes in the future (it will!) we can just change teh SYNONYM, and not have to modify (and re-test) all the code.
Also, we never use a JOIN between RemoteServer and LocalServer (JOIN between two different local databases is OK). I don't know whether "never" is too strong a rule, but we avoid it because of the risk of SQL pulling ALL the data from Remote and THEN figuring out the JOIN locally.
We do keep a local staging-table copy of remote data, locally, to then JOIN, and we freshen up the staging table (as efficiently as we can / with minimum amount of data moving across the WAN) once a day / whatever.
