SQL Server Agent control

Hot Topic

Can *datastore.xml be used to populate template.ini as a substitute for setup.iss?
RUNNIG A JOB???
The report server cannot open a connection to the report server database
Make it easier to add a data viewer
SSIS error message when trying to open MSDB in management studio
Uninstallation problem of Beta VS2005 MSDN and SQL 2005
efficient JOIN query
Excel Destination Error: Column"xx" cannot convert between unicode and non-unicode string data types
SQL Server 2005 Problems
OLE DB timeout processing AS Cube

SQL Server

SQL Query in Script
Deploy Reports
Restoring SQL Server 2005 backup to SQL server 2000
sql2005:Cannot use SAVE TRANSACTION within a distributed transaction
Business Intelligence Development Studio not installing from Toolkit
SSIS - Add a new SQL Server Mobile Destination
Win 2003 x64 - SSIS error Importing xls and access data
error changing subscription properties
Select date year below 1950 not valid?
How automatically process a Cube periodically

SQL Server Agent control

I have a SSIS package that is scheduled to run at the same time every hour/day/whatever using SQL Server Agent. If the package fails then I don't want to run the package again until I've fixed the problem. For this reason i want to disable the agent job at the start of the package and then enable it again at the end.

Has anyone done any work on controlling SQL Server Agent jobs from within an SSIS package
How have you done it

Any advice would be much appreciated.

I feel a custom task coming on... Smile   Is there an API for SQL Server Agent

Thanks
Jamie



Answer this question

SQL Server Agent control

  • rfiddelke

    So far my list of custom components is as follows:

    Enhanced Error Component to provide the column name that caused a row to fail (almost complete)
    SQL profiler log file Source. Reads SQL Profiler files
    Split Transform. That can split a string into tokens.
    Log parser source. To process log files
    IdentityInsert Transform. Bulk Inserts data into a SQL table and provides the identity values that are generated.

  • AlexeyK

     SimonSa wrote:
    I'd go for the sp route. The one you want is

    sp_update_job



    Yeah, but I think is a nice easy candidate for a custom task. Watch this space!


  • gameboy_advance

    You could use the sp's to change the job, either in an Exec SQL task or perhaps two extra steps in the job, before and after the DTS calling step.

    SMO would be the API for this sort of stuff, although you could probably use DMO still.



  • sharpbart

    I'd go for the sp route. The one you want is

    sp_update_job

  • SQL Server Agent control
Answer this question