August 10, 2012 at 5:40 pm
Anyone here in this forum use Control-m software to excute SQl agent jobs?
We currently use SQL agent to run SSIS packages.
But we have this Control-m software and used at first by our Oralce jobs, now network admin would like to extend to use for SQL Server.
The good point of using Control-M, is you can see all the jobs in one place in the organization. and make the precedence and order of execution of jobs one after another.
In Control-M, you have to return a successful or failure code to it.
We tried this way : use the comand line .bat file to call dtexe:
Something like below
@echo off
dtexec /FILE "C:\SendProduct.dtsx" /CHECKPOINTING OFF /REPORTING EWCDI
exit /b %errorlevel%
This works fine, the problem is because this is no longer a SQL agent job, so no job history is created in SQL server and no notification is sentto me. It is hard for me to track it. I think control-m manager they can do that in the application.
I would like to find out if any DBA use this software. Any pros and cons.
It will be nice if it can directly start a sql agent job.
But it seems control-M so far has no a decent way to work with SQl agent.
Thanks,
August 12, 2012 at 12:31 am
I think you can define the shout condition for "NOTOK" in case of the job failure and "LONGRUNNING" if it runs longer than expected. ControlM does keep a log for all jobs kicked off in its log directory as well.
August 13, 2012 at 10:55 am
xia.wu.1983 (8/12/2012)
I think you can define the shout condition for "NOTOK" in case of the job failure and "LONGRUNNING" if it runs longer than expected. ControlM does keep a log for all jobs kicked off in its log directory as well.
But how to return and define job failure and longrunning?
It seems there is no direct interaction between control-m and sql agent job.
August 15, 2012 at 10:26 pm
Although you can trigger a job within SQL Agent from Control M, you then have the complexity of getting the Control M job to wait until the SQL Agent job completes, and then getting the result of that execution back to Control M.
To trigger the job, use an OSQL.exe call with sp_start_job.
Then sp_help_job to get the job status (running in a loop until the job finishes)
You should also return the Success/Failure text from the SQL Agent job back to Control M.
It has been a couple of years since I last used Control M, but this is how it was used at my previous site:
SQL Agent was not used
Run the job as you noted as a call to DTEXEC.exe
Use Control M's ability to track sucess and failure, and then SMS/Pager/eMail on Failure
Use Control M's ability to track long running, and then SMS/Pager/eMail on Failure
Use Control M's history to get details on execution, and logs.
Use SSIS' Logging interface to keep track of execution times etc., if Control M's history is insufficient
Please note that this philosophy is relevant to any scheduling system like Control M, Tivoli, Active Batch, etc.
Pros:
Central View of ALL jobs (regardless of SQL Instance/Oracle/Unix/Windows/etc.), and their current status **This is a big thing for medium to large companies**
Dependancies between jobs (eg. backups can wait for data load to complete)
Cross Platform/Instance dependancies are possible
Cons:
Job execution history isn't in Management Studio
More complex to get the jobs into production, ie. may have to get someone else to setup the job definitions
Keith
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply