Can Qlik Cloud app reload be triggered only after a SQL Server Agent job completes?

[Qlik_Wizard]

Hi everyone,

We have a SQL Server Agent (SSMS) job that loads data into our database, and we want our Qlik Cloud app to reload only after this SQL job has completed successfully.

Is there a native way in Qlik Cloud to make an app reload wait for or depend on a SQL Server Agent job?

If not, what is the recommended approach? Has anyone implemented this using:

• Qlik Application Automation
• Qlik Cloud Reload APIs
• SQL Server Agent calling the Qlik API after completion
• An external scheduler/orchestration tool

We'd like to avoid scheduling the reload with a fixed time buffer and instead trigger it only after the SQL job finishes successfully.

Any recommendations or examples would be appreciated. Thanks!

Qlik Cloud Qlik Data Gateway  

[Levi_Turner]

I've not done it but option 3 (SQL Server Agent calling the Qlik API after completion) is the most natural to my eyes. The orchestration is being done by SQL Server Agent thus it makes sense to have it be the orchestrator of the next step. In terms of where I'd go, I'd explore calling a PowerShell script, examples:

https://learn.microsoft.com/en-us/powershell/sql-server/run-windows-powershell-steps-in-sql-server-a...

https://www.sqlservercentral.com/articles/creating-sql-agent-jobs-to-run-powershell

With something as simple as:

qlik app reload --app ec87a420-a1ca-4dc1-a311-7a48bc41f789

(using qlik-cli)

Since qlik-cli's stock setup relies on reading server URLs and API keys from ~\.qlik\contexts.yml, you can alternatively hard code in values in the command line like so:

qlik app ls --server https://{my_tenant_url} --headers Authorization='Bearer {my_api_key}'

[Qlik_Wizard]

Thanks, Levi. That approach makes sense.

I'm new to Qlik Cloud and qlik-cli, so I'm still trying to understand how all the pieces fit together.

Would you happen to have an example of the PowerShell script that you've used (or one that you know should work) to trigger a Qlik Cloud app reload after a SQL Server Agent job completes?

For example, I'd be interested in seeing:

• How you authenticate to Qlik Cloud (API key/context).
• The qlik-cli command (or REST API call) to trigger the reload.
• How the PowerShell script returns a success/failure code back to SQL Server Agent if the reload request fails.

Even a simple working example would help me understand how to implement this.

[marksouzacosta]

Hi @Qlik_Wizard,

There are multiple ways to solve this problem. One that I use in multiple scenarios is to create a Stored Procedure to call Qlik API to reload an application. Here is a SQL script example:

/*
-- To enable Ole Automation Procedures
-- Code only needed if the Ole Automation Procedures is not enabled

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'Ole Automation Procedures', 1;  
GO  
RECONFIGURE;  
GO  

-- Qlik Reloads API Reference
-- https://qlik.dev/apis/rest/reloads/#post-api-v1-reloads
*/

-- Required Parameters
DECLARE @AppId AS Varchar(200) = 'your-application-guid'; -- REQUIRED: Enter the AppId to call the reload process
DECLARE @Url as Varchar(8000) = 'https://your-tenant.us.qlikcloud.com/api/v1/reloads'; -- REQUIRED: Change according to tour Tennant URLs
DECLARE @Body as varchar(8000) = CONCAT('{"appId": "',@AppId,'","partial": false}');
DECLARE @ApiKey AS VARCHAR (8000) = 'Bearer your-api-key'; -- REQUIRED: The API Key generated on QMC

DECLARE @Object as Int;
DECLARE @ResponseText as Varchar(8000);

EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT;
EXEC sp_OAMethod @Object, 'open', NULL, 'post', @Url, 'false'

EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/json'
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Authorization', @ApiKey
EXEC sp_OAMethod @Object, 'send', null, @Body

EXEC sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
SELECT @ResponseText

EXEC sp_OADestroy @Object

Note: you can use this same code to do multiple things with Qlik Cloud API, like running a Qlik Automate.

Regards,

Mark Costa