June 30, 2017 at 2:58 am
Hi,
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.)?
Best regards
Nicole;)
June 30, 2017 at 6:04 am
info 58414 - Friday, June 30, 2017 2:58 AMHi,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.)?Best regards
Nicole;)
I would suggest re-engineering your solution a little.
Here is one way you could do it
1) Data flow from staging table to (newly truncated) work table in DWH
2) ExecuteSQL task to merge from work table to target table in DWH.
If only certain rows are required, perhaps based on some 'date modified' column, use a refined version of the above:
1) ExecuteSQL task in DWH to get MAX(DateModified) and assign to variable MaxDateModified
2) Data flow from staging table where DateModified >= MaxDateModified to (newly truncated) work table in DWH
3) ExecuteSQL task to merge from work table to target table in DWH.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 30, 2017 at 4:12 pm
I'll also suggest that you should never use more than 2 part naming in any stored procedure. If you ever change to a different database name, you'll have a wad of code to modify. Instead, use two part naming to call on a synonym that points to the desired target. If you have to move code to, for example, a development database where the naming convention of linked servers and/or other databases is different, all you'll need to do is change the synonyms and you're ready to rock
--Jeff Moden
July 3, 2017 at 8:56 am
Jeff Moden - Friday, June 30, 2017 4:12 PMI'll also suggest that you should never use more than 2 part naming in any stored procedure. If you ever change to a different database name, you'll have a wad of code to modify. Instead, use two part naming to call on a synonym that points to the desired target. If you have to move code to, for example, a development database where the naming convention of linked servers and/or other databases is different, all you'll need to do is change the synonyms and you're ready to rock
+1. I highly dislike tight coupling that creates future architectural challenges.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply