Alternative to linked server using SSIS?

Aidan Devlin 21 Reputation points
2023-04-04T05:47:50.55+00:00

Hi, I'm currently using a merge statement from many tables on server 1 to update a table on server 2, roughly 800 million rows. the current execution time is anywhere between 5 and 7 hours. The data is moving across servers and the the statements are using linked servers. Is there an SSIS package that will merge the data and update the the table on server 2 to ensure there are no duplicates without using linked servers? Thanks, Aidan

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,910 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,468 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. ZoeHui-MSFT 33,626 Reputation points
    2023-04-05T05:47:48.97+00:00

    Hi @Aidan Devlin,

    If the two servers are on the same domain or same machine, no linked servers are needed, SSIS can connect to the databases directly. Please have a double check.

    You may use Lookup Transformation to ensure there are no duplicates.

    If I misunderstand your issue, please incorrect me.

    Regards,

    Zoe Hui


    If the answer is helpful, please click "Accept Answer" and upvote it.

    0 comments No comments