Periodically run query to transfer data between two SQL Servers

Question

I have two MS SQL Servers, let's call them Source and Destination. I need to bring some data from a database table in Source by making a simple query (a Select and a Sum) and bring the result to a database table in Destination.

I need to run the query once a month in order to have available the data in Destination to be used by an application.

I was thinking about doing it at application layer by creating a workflow (which is complex and not worth for such a simple issue) but I would like to know whether there is a easier option by using SSIS or any other tool. It is especially unclear to me how to set up the trigger to run the query and where to place the query code.

Answer 1

You should just make use of the SQL Server Job scheduling functionality rather than writing your own triggering and handling logic.

And creating an SSIS package (or dtsx file) is a good option. You might just need to do an import/export wizard out of the database tasks, save it as a reusable SSIS package and then use a scheduled job to run it once a month.

So, the job is your trigger and also calls the SSIS import/export package to run... your query code.