Microsoft SQL Server Agent Jobs

Owned by Stonebranch (Deactivated)
Last updated: Jul 12, 2024
5 min read

Disclaimer

Your use of this download is governed by Stonebranch’s Terms of Use, which are available at  Stonebranch Integration Hub - Terms of Use.

Overview

SQL Server Agent job helps to schedule and execute tasks within the SQL Server environment. For example, you can trigger nightly backups, SSIS package executions, and Database applications and administration activities.

This extension simplifies interacting with SQL Server Agent jobs. It allows users to:

  • Browse and Select Jobs - Easily list and choose the desired SQL Server Agent job and its specific steps.

  • Automated Execution - Trigger the selected job on the Microsoft SQL Server.

  • Monitoring and Logging - Monitor the job's execution status and retrieve logs upon completion, providing insights into the process.

The dependencies of the SQL server agent jobs with other platforms or applications can also be managed easily via UAC workflows.

Version Information

Template Name

Extension Name

Extension Version

Template Name

Extension Name

Extension Version

CS MSSQL-Server-Agent 

ue-cs-mssql-server-agent

1.0.0

Refer to Changelog for version history information.

Software Requirements

This integration requires a Universal Agent and a Python runtime to execute the Universal Task.

Software Requirements for Universal Template and Universal Task

Requires Python 3.7.6 or higher. Tested with the Universal Agent bundled Python distribution.

Software Requirements for Universal Agent

Both Windows and Linux agents are supported.

  • Universal Agent for Windows x64 Version 7.4.0.0 and later with Python options installed.

  • Universal Agent for Linux Version 7.4.0.0 and later with Python options installed.

  • Requires "pymssql" python module to be installed in the Agent host 

  • This integration connects to the MSSQL server via pymssql 

    • Check if the stored procedure "UAC_START_STOP_MSSQL_AGENT_JOB" exists before the SQL server agent job is triggered. If it doesn't exist, then it creates the stored procedure 

Software Requirements for Universal Controller

Universal Controller Version 7.4.0.0 and later.

Network and Connectivity Requirements

Key Features

This Universal Extension provides the following key features:

  • Dynamic Choice field to list the Database(select msdb), SQL server Agent job, and SQL Server Agent Job step name

  • Trigger a MSSQL Server agent job

  • Trigger and Wait until the SQL server agent Job Reaches the status "Success" or "Failed"

  • Read the status of an existing Batch Job

  • Restart the job from a specific step if failed

Import Universal Template

To use the Universal Template, you first must perform the following steps.

  1. This Universal Task requires the Resolvable Credentials feature. Check that the Resolvable Credentials Permitted system property has been set to true.

  2. To import the Universal Template into your Controller, follow these instructions.

  3. When the files have been imported successfully, refresh the Universal Templates list; the Universal Template will appear.

Modifications of this integration, applied by users or customers, before or after import, might affect the supportability of this integration. For more information refer to Integration Modifications.

Configure Universal Task

For a new Universal Task, create a new task, and enter the required input fields.

Input Fields

The input fields for this Universal Extension are described below.

Field

Input type

Default value

Type

Description

Field

Input type

Default value

Type

Description

Action

Required

Run a SQL Server Agent Job

Choice

Action performed upon the task execution. Available actions:

  • Run a SQL Server Agent Job

MS SQL Server Name

Required

 

Choice

Provide the IP address or the MSSQL Server Hostname

MS SQL DB Credentials

Required

 

Credentials

Credentials for accessing the MSSQL Server. The Credentials definition should be as follows.

  • User as "Runtime User" -  SQL or Windows User

  • User Password as "Runtime Password".

SQL Agent Job Name

Required

 

Choice

Specify the SQL server agent job name or use dynamic choice to list the SQL server agent job

Step Name

Required

 

Choice

Specify the SQL server agent job's Step name or use dynamic choice to list the step name associated with the Job

Task Examples

Dynamic Choice to fetch the SQL Server Agent Job Name :

 

 

Dynamic Choice to fetch the SQL Server Agent Job's Step name :

Launch SQL Server Agent Job :

 

Example SQL Server Agent Job in Workflow :

 

Exit Codes

The exit codes for this Universal Extension are described below.

Exit Code

Status Classification Code

Status Classification Description

Status Description

Exit Code

Status Classification Code

Status Classification Description

Status Description

0

SUCCESS

Successful Execution

SUCCESS: Successful Task execution

1

FAIL

Failed Execution

Failed in launching the SQL server Agent Job or the SQL server job failed execution

STDOUT and STDERR

STDOUT and STDERR provide additional information to the User. The populated content can be changed in future versions of this extension without notice. Backward compatibility is not guaranteed.

Document References

This document references the following documents.

Document Link

Description

Document Link

Description

Universal Templates

User documentation for creating, working with, and understanding Universal Templates and Integrations.

Universal Tasks

User documentation for creating Universal Tasks in the Universal Controller user interface.

Credentials

User documentation for creating and working with credentials.

Resolvable Credentials Permitted Property

User documentation for Resolvable Credentials Permitted Property.

Integration Modifications

Modifications applied by users or customers, before or after import, might affect the supportability of this integration. The following modifications are discouraged to retain the support level as applied for this integration.

  • Python code modifications should not be done.

  • Template Modifications

    • General Section

      • "Name", "Extension", "Variable Prefix", "Icon" should not be changed.

    • Universal Template Details Section

      • "Template Type", "Agent Type", "Send Extension Variables", "Always Cancel on Force Finish" should not be changed.

    • Result Processing Defaults Section

      • Success and Failure Exit codes should not be changed.

      • Success and Failure Output processing should not be changed.

    • Fields Restriction Section
      The setup of the template does not impose any restrictions, However with respect to "Exit Code Processing Fields" section.

      1. Success/Failure exit codes need to be respected.

      2. In principle, as STDERR and STDOUT outputs can change in follow-up releases of this integration, they should not be considered as a reliable source for determining success or failure of a task.

Users and customers are encouraged to report defects, or feature requests at Stonebranch Support Desk.

Changelog

ue-cs-mssql-server-agent-1.0.0 (2024-07-03)

Initial Version

 

 

 

 

 

 

 

 

Â