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".
Best Answer
If You already install SQL BI Pack for VS 2013 - http://www.microsoft.com/en-nz/download/details.aspx?id=42313
for create SSIS package You just need:
Main idea of SSIS (and similar tools) - not only load data from server to server, but transformation and check on the fly, and some of this transformations hard (or impossible) made by SQL only tools.
Bulk insert in SSIS mean load from text file, when You not need any transformation
Linked Server is good solution if Your servers have good network connections, it allow You not worry about 3rd party tools (SSIS as well 3rd party relate to SQL Server engine).
Other way - unload result of complicated SELECT into text file and use bulk load resulted flat file into destination server.