2

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".

Improve this question
3
  • 1
    See if your DBA can setup a linked server on the destination SQL Server instance that has SELECT access to the source DB tables you need to get the data from, then write your query or SSIS package to SELECT that data from the source on the destination 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.
    – IT Thug Ninja
    Nov 18, 2015 at 15:35
  • I am the DBA and I would prefer not using linked servers.
    – Morten Nilsen
    Nov 19, 2015 at 15:31
  • So, is integration services not used by anyone? I thought my use case was exactly what SSIS was designed for.
    – Morten Nilsen
    Nov 23, 2015 at 13:50

2 Answers 2

Reset to default
1

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:

"Create New Project" -> "Business Intelligence" - > "Integration Services Project"

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.

Improve this answer
1
  • I have tried finding that download - it is incredibly hard to find the right stuff with microsoft. Thanks for linking it! I already created SSIS packages using the import data wizard. Editing them became a whole lot easier now :)
    – Morten Nilsen
    Nov 26, 2015 at 10:43
1

BCP utility has an option to export the result of a query to a file (queryout). I would suggest packaging the complex query as a stored procedure to separate the "what" from the "how". Then use BCP to import it at source, or BULKIMPORT, depending on the file format you choose.

BCP is just a command line program so it can be scheduled from outside SQL Server or from SQL Agent.

Agent can also run SSIS packages directly. The Import /Export Wizard may be able to create the SSIS package.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.