CODEWITHSUNDEEP
sqlsql-server-2008windows-servicesetl
Vaibhav
Vaibhav

Reputation: 6998

How to start a job or process when a job on another server finished?

I want to send a mail or automatically start a job on my server as soon as a job on another server has finished successfully. I have access to the other server and can view the job status but I cannot change the job itself, which is running an SSIS package.

Basically, I want to start refreshing my database (via running an ETL through job) as soon as source has stopped refreshing itself. I would love to have suggestion beside this windows service implementation.

Upvotes: 2

Views: 1381

Answers (1)

Pondlife
Pondlife

Reputation: 16240

I took the liberty of editing the question and title to make it more explicit. As I understand it from your description, you want to run a job (or start some other process) on server A when a job on server B has completed successfully. You cannot change the job definition on server B, but you can log on to it and view the job history.

If you can't change the job or anything else on server B, that means it cannot notify server A when the job is complete. Therefore, you need to query server B from server A, using a Windows service or possibly a simple script that runs every few minutes (or hours, or whatever is appropriate).

You can query the status of a job from .NET or PowerShell using the SMO Job class, or from TSQL using the sp_help_job procedure. Which of these is a better solution depends on how you want to implement your polling mechanism.

Upvotes: 1

Related Questions