May 17, 2019 at 4:18 pm
We have data residing in SQL Server 2017, Oracle and Teradata. Without first staging the Oracle and Teradata data into SQL Server, is it possible to code a single t-sql statement (in an SSIS EXEC SQL Task) which will join all 3 data sources in 1 query? Would this require advance setup of Linked Servers or can I use other connection types (eg. predefined Attunity connection to Oracle)
Also, I've been asked, when the t-sql statement is issued, is ALL the data from the remote data sources (Oracle and Teradata) returned to SQL into an intermediary temp table then qualifiers applied to render final result set -- or is the data prequalified at the data source and only a small set of rows are returned to SQL Server for further qualification.
If anyone can point me to a good article on this, it'd be greatly appreciated.
May 17, 2019 at 5:08 pm
with linked server you can join all 3 sources - but DO NOT DO IT!!!
As you read it and mentioned on your post it will copy the data from the remote servers to local temp tables and then do the joins.
Any filtering that can be done on the individual sources may be applied but not all and not always.
As SQL will copy the sources to local tables its better that you do that yourself explicitly as it will enable you to create any supporting indexes on the temp tables.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply