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.

Thanks in advance

Francesco

Answer 1

Create a package using SSIS to transfer the data. Then create a scheduled job using the SQL Agent to execute the SSIS package on your defined schedule.

This is a pretty simple and common task. Using SSIS and SQL Agent for scheduled execution is the most common way to do this.

You are correct that there's no need to go the complex route of creating your own tool when one is provided for you as part of the database server....

To understand and learn more about SSIS start with Books Online, there are numerous SSIS tutorials within it. One of the first tutorials will more than likely cover your exact scenario.