Tutorial : Start a job via the Orchestrator API on Microsoft SQL Server

Hello community !

In this tutorial I’ll explain how to start jobs on SQL under MSSQL but you can easily adapt the code to perform any kind of Orchestrator api requests from MSSQL.

This tutorial with a detailed wiki (soon for wiki) is available here too.

You’ll need two steps :

  • Register an external app on Orchestrator

  • Run the SQL Code

At the end, you’ll be able to start a job with only this type of commands :

EXEC	[RPA_OrchestratorAPI_StartJob]
		@processName = N'YOUR_PROCESS_NAME',
		@organization = N'YOUR_ORGANIZATION_NAME',
		@processPathFromEnvFolder = N'PATH_TO_PROCESS'

I saw a similar post on this topic a few days ago which is here but this tutorial go a bit deeper as it explains every steps of the process and manages the api credentials storing in SQL tables (crypting with keys and certificate), which is more viable than writing it in clear in code.

1 - Register an external app on Orchestrator

In order to request Orchestrator Cloud API, you’ll need to register your client as an external application on your Orchestrator.

Connect on your Orchestrator and go under Admin → External Applications :

image-1d3f9a6e-adcd-4d87-b478-27373961788a

Then click on image-e40936d9-2abb-49d2-809c-cf42741cd80f

Then fill in The Application Name.
Then chose Confidential Application.

Then click on image-34374990-65c6-4bab-ad21-ca24a4e4fe48

Select Orchestrator API Access and go under Application Scope (that’s important to determine which OAuth workflow your app will use to authenticate) :

image-f5fc8149-ba41-4425-9930-d3fe74dc94da

Then select the above Scopes and click Save :

  • OR.Jobs.Write
  • OR.Execution.Read

Leave the Redirect Url field empty.

Then Save your new External App.
A window appears with your app ID and Secret, keep it, we’ll need it for next steps.

2 - Run the SQL Code

Getting scripts from Github :

Go on this Github repository and clone or download the project.

Edit config files :

When it’s done, go edit the two files :

  • config/CREATE_MASTER_KEY.sql :

    • Replace YOUR_DATABASE by your database name
  • config/FILL_TABLE.sql

    • Replace YOUR_ORCHESTRATOR_ORGANIZATION_NAME by your organization name

    • Replace YOUR_ORCHESTRATOR_TENANT_NAME by your tenant

    • Replace YOUR_EXTERNAL_APP_ID by the app id from step one

    • Replace YOUR_EXTERNAL_APP_SECRET by the app secret from step one

Run tables and stored procedures creation :

Now, you just have to run either :

  • create_on_mssql.cmd from a terminal if you authenticate to MSSQL under SQL Server Authentication
  • or create_on_mssql_windows_auth.cmd from a terminal if you authenticate to MSSQL under Windows Integrated Authentication

Note : to run those scripts, you’ll need :

  • EXECUTE privilege on those four system procedures (you’ll need it to start jobs later too):
    • sp_OACreate
    • sp_OAMethod
    • sp_OAGetProperty
    • sp_OADestroy
  • CONTROL permission on the database.
  • CREATE CERTIFICATE permission on the database. Only Windows logins, SQL Server logins, and application roles can own certificates. Groups and roles cannot own certificates.

That’s it, you’re ready to start UiPath processes from MSSQL !

Now you can test it running :

EXEC	[RPA_OrchestratorAPI_StartJob]
		@processName = N'YOUR_PROCESS_NAME',
		@organization = N'YOUR_ORGANIZATION_NAME',
		@processPathFromEnvFolder = N'PATH_TO_PROCESS'

With PATH_TO_PROCESS beeing the complete path to the folder where your process is.
E.g. Staging/TestLoggingSite for a process located here :

That’s it !
Don’t hesitate to ask questions if it bugs or if some points are unclear.

2 Likes

This topic was automatically closed after 30 days. New replies are no longer allowed.