Automation from log server to reporting server

[03Explorer]

Currently I have to execute a SQL code that is 1000's lines of code that reads a log file. The purpose of the script is to consolidate data based on day and buckets (which there are over 50 buckets). This process returns values in a layout that is foreign to the originating log file. This output is sent to a flat file with header and then DTS reads the flat file and populates into a different database (one that is not associated with the heavy activity of the logging db).

Problem is the generating of the flat file takes between 4 and 6 hours to generate (because it handles a full months worth of data). The rest is quickly executed in about five minutes.

What I would like to do is have the dts execute the query itself and populate our reporting server daily, eliminating the once a month 4-6 hour process. Where should I look or study to make this be that automated (keeping in mind I don't want any relying on the workstation I execute the SQL code monthly).

OR... is there something I am not considering that would be better.

 

[SQLSister]

Since you have a script, add an execute SQL task to the start of the DTS package and then connect it to the next task with an onsuccess conection.

Of course, try this out on dev first. Not sure if running daily will materially speed up your process, it will depend on what the task actually has to do. SO mulitple tests over several days would be required.

"NOTHING is more important in a database than integrity." ESquared