Best way to design this package

Question

Post reply

Best way to design this package

Shark Energy

SSCrazy Eights

Points: 8389
More actions
August 27, 2010 at 9:39 am

#225325

We have a mirroring product (third party) that keeps a database up to date, and logs in a table when it has finished (generally updates every 5 minutes).
At the end of each week, this product tends to fall over (due to a separate topic/subject i.e. don't worry about that) and we need it to be running for end of week processing. So I have a stored procedure I have put together that constantly monitors the log table to ensure the timestamps are moving on, and if not.....
I want to kick the service off again. Now the question is - how best to do this?
I have an idea of running the Stored Proc in an execute SQL task, and then have 4 containers (1 for each of the 4 services) that look for a variable set by my findings in the stored proc. If "KickstartService" is set to True then that batch file is kicked off. However, I am slightly stumped as I can't think of a way of having the job that starts the service "reset" and continue to monitor once it has run the batch file.
Is there a way I could use a ForEach Container that checks for a value, when the value is met it runs a batch file, and then the value is reset so it continues until a final value is met (a "stop monitoring" variable)
I've been working on the groovy stored proc all afternoon so my brain is now fried.
Thanks for any tips! :hehe:

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

Why not just make a job that runs the package every minute / 5 minutes, etc.?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 2

I could do that, but I would have to store the data in a permanent table.

What I have done so far is to create a table variable that stores the Most recent timestamp and the previous timestamp, with a datediff computed column. If the gap between the timestamps becomes less than a set period then I go through the routine of kicking starting the service.

I have an idea (but not sure it will work - about to test) to have a bunch of For Loop containers sitting in the same package and they keep running until a variable changes (@Scan). Meanwhile inside the loop an execute sql task keeps scanning for another variable - if met it will run step 2 - kicking off the service. Then I guess the loop will see it go back to Stage 1, scanning for the same variable again...

Not quite sure what the impact would be but doesn't seem like it would be too bad.

Phil Parkin SSC Guru Points: 247180 More actions · Answer 3

That sounds overly complex to me. If a package is scheduled to run every x minutes, can't it just calculate the time between the last log entry and 'now' and, if it's greater than it should be, restart the service?

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 4

it gets a little more complex that that as this product can sometimes drift x amount of minutes behind. So i need a way of saying, has the timestamp moved at all in the last x minutes.

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 5

actually i should have made it clearer - the timestamp does not represent the time it was last updated. It is the system time from the db we retrieve the data from so we know how up to date things are. It can be behind during heavy processing but it always ticks along however slowly.