Given two MS SQL servers, each with a different database, how does one create an SSIS package such that one can run a complex query that fills a temporary table and then transfer that data as a bulk insert into a permanent table on the other server?
Today this is done by running an external program that generates insert statements for each row, but this is slow and results in a lot of transaction logging.
I have been looking for resources on how to do this online, but as per usual, MS docs are less than helpful.
I have visual studio 2013 and 2015, but am unable to find any SSIS package creation tool, even after install "DACFramework" and "SSDT".
destination
SQL Server instance that hasSELECT
access to thesource
DB tables you need to get the data from, then write your query or SSIS package toSELECT
that data from thesource
on thedestination
SQL Server instance so the tempdb is built on that same instance with going across the linked server. I'm not sure how complex your code is of if any of it is T-SQL within the SSIS package, but if you provide an example, it may be able to be written another way to accomplish what you're trying to do here.