Skip to main content

Execute SQL in Azure SQL DB (via Service Principal)

Execute SQL in Azure SQL DB (accessed via Service Principal). The SQL may request an email to be sent and/or the Task to fail.

To use this activity within the API, use an ActivityCode of EXEC-AZSQL-SP.

Example JSON​

An example of what the Task Config would look like for a task using this activity. Some of these variables would be set at the group level to avoid duplication between tasks.

{
    "DatabaseConnectionStringKeyVaultSecretName": "<<CustomerMasterDataDBConnectionStringKeyVaultSecretName>>",
    "QueryToExecute": "EXEC dbo.up_MyProcedure"
}

Variable Reference​

The following variables are supported:

  • TargetConnection - (Optional) Target connection to use.

  • QueryToExecute - (Required) Query to execute against target database. Can be any valid SQL statement.

  • EmailTemplate - (Optional) The template to be used to format the email.

  • EmailRecipients - (Optional) The semi-colon separated list of recipients of the email.

  • EmailSubject - (Optional) The subject of the email.

  • EmailMessage - (Optional) The body of the email.

  • ExtractControlVariableName - (Optional) For incremental loads only, the name to assign the Extract Control variable in State Config for the ExtractControl value derived from the Extract Control Query above.

  • ExtractControlVariableSeedValue - (Optional) The initial value to set for the Extract Control variable in State Config - this will have no impact beyond the original seeding of the Extract Control variable in State Config.

  • MaximumNumberOfAttemptsAllowed - (Optional) The total number of times the running of this Task can be attempted.

  • MinutesToWaitBeforeNextAttempt - (Optional) If a Task run fails, the number of minutes to wait before re-attempting the Task.

  • IsFederated - (Optional) Makes task available to other Insight Factories within this organisation.

  • Links - (Optional) NULL