3

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.

CiccioMiami
  • 8,028
  • 32
  • 90
  • 151

2 Answers2

5

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.

Paul Sasik
  • 79,492
  • 20
  • 149
  • 189
  • Forget job scheduling - Replication is a built-in feature. – Joel Coehoorn Jun 29 '11 at 14:04
  • 2
    @Joel: Jobs are a built-in feature as well. Replication would work too but I think it's overkill in this case and would actually be tougher to use with a *Sum* aggregate which was mentioned as a requirement by the OP. Using jobs also allows you to just manage a single job object on one server. Replication requires non-trivial configuration on both ends. – Paul Sasik Jun 29 '11 at 14:06
  • Thanks for your answer. Shall I use the Execute Package Utility? – CiccioMiami Jun 29 '11 at 14:10
  • and where shall I place and execute the package? Source or Destination server? – CiccioMiami Jun 29 '11 at 14:20
  • @Francesco: You shouldn't need any special utility if you create a job to run your package. That will be your utility. And I would put the job on the "destination" server. It's often easier and less problematic to pull (instead of pushing.) – Paul Sasik Jun 29 '11 at 14:51
0

An alternative to a scheduled job/SSIS solution would be to create a view in Destination that maps back to Source via a linked server and openrowset call. This way you don't really need to move the data, and can access it when you like in real-time from Source.

Richard Schweiger
  • 1