Schedule package in Azure Integration Services (ADF V2)

Case
I have deployed my SSIS project to an SSIS Catalog in Azure (Data Factory) and now I want to schedule the package to run each day at 7:00AM. How do I do that?

How to schedule this SSIS package in Azure?

Solution
Microsoft suggests the following options:

• SQL Server Agent. Here you use an on-premises SQL Server Agent to schedule the SSIS package in the cloud. This sounds like the least attractive solution. However if you still have a lot of on-premises projects that all run via SQL Server Agent then it is easy to use this solution and have all scheduled executions on one location.
• SQL Database elastic jobs. This requires adding an extra component to your subscription, but if you already use a SQL Elastic database pool then this could be a good solution. Will try this in a future post.
• Azure Data Factory. Since we already use ADF V2 to host the Integration Runtime, this is most obvious solution. It executes a stored procedure that executes the package.

This post explains the ADF solution with the stored procedure, but executing an stored procedure can be done in various other Azure components such as a Runbook in Azure Automation, Logic Apps or even Azure Functions with a trigger on arriving new files in a Blob Storage container.

*update 13-04-2018: new Execute SSIS Package activity*

1) ADF V2 - Author & Monitor
Go to your Azure Data Factory that hosts your SSIS Integration Runtime. Under Quick links, click on the Author & Monitor link. A new tab will be opened with the Azure Data Factory dashboard. Next click on Create Pipeline circle and go to the next step.

Azure Data Factory dashboard

2) Create pipeline with Stored Procedure
You just created a new pipeline in the previous step. Give it a descriptive name (like 'SSIS Job MyAzureProject') in the General tab. A description is optional. Next collapse the General activities and drag a Stored Procedure to the canvas. Again, give it a descriptive name in the General tab.

Add Stored Procedure to Pipeline

3) Linked service
Next step is to create a linked service to the SSISDB to execute the Stored Procedure. Go to the SQL Account tab and add a new Linked service. Point it to the SSISDB that hosts the package that you want to execute. Hit the test button after filling in all fields to make sure the everything is correct. Then click Finish and continue to the next step.

Most important fields of the Linked service

Add Linked service

4) Creating Stored Procedure call
The code to execute a package consists of multiple Stored Procedures. To keep it simple we will use sp_executesql to execute a string of SQL code containing all Stored Procedure calls. You can easily create the SQL for this in SSMS.

Go to your package in the Catalog. Right click it and choose Execute... Now set all options like Logging Level, Environment and 32/64bit. After setting all options hit the Script button instead of the Ok button. This is the code you want to use. You can finetune it with some code to check whether the package finished successfully.

Generating code in SSMS

The code below was generated and finetuned. Copy your code we need it in the next step.

-- Variables for execution and error message
DECLARE @execution_id bigint, @err_msg NVARCHAR(150)

-- Create execution and fill @execution_id variable
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'SSISJoost', @project_name=N'MyAzureProject', @use32bitruntime=False, @reference_id=Null, @useanyworker=True, @runinscaleout=True

-- Set logging level: 0=None, 1=Basic, 2=Performance, 3=Verbose 
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=1 

-- Set synchonized option 0=A-SYNCHRONIZED, 1=SYNCHRONIZED 
-- A-SYNCHRONIZED: don't wait for the result
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'SYNCHRONIZED',  @parameter_value=1 

-- Execute the package with parameters from above
EXEC [SSISDB].[catalog].[start_execution] @execution_id, @retry_count=0

-- Check if the package executed succesfully (only for SYNCHRONIZED execution)
IF(SELECT [status] FROM [SSISDB].[catalog].[executions] WHERE execution_id=@execution_id)<>7
BEGIN
 SET @err_msg=N'Your package execution did not succeed for execution ID: ' + CAST(@execution_id AS NVARCHAR(20))
 RAISERROR(@err_msg,15,1)
END

The last part of this T-SQL code is very useful. It will cause an error in de pipeline monitor of ADF when a package fails. When you click on the text balloon it will show which SSIS execution failed.

Showing when a package fails in the Pipeline monitor

5) Adding Stored Procedure
Go to the Stored Procedure tab and enter a Stored Procedure name manually by first checking the Edit option. Then enter sp_executesql. Then add a new parameter with the name stmt (type string) and paste the complete code of the previous step the value field. After this we are ready to test and schedule this ADF Pipeline.

Add Stored Procedure code

6) Publish and trigger
Now it is time to test the Pipeline, but first hit the Publish All button (on the left side) to publish your new pipeline to ADF. Then click on the Trigger button and choose Trigger Now to execute the pipeline immediately.

After that click on the Monitor button to check your execution and/or go to the Catalog and open an execution report to see the result.

Publish and trigger manually

7) Schedule
If everything went well it is time to schedule your package. Go back to the Author page by clicking on the pencil (on the left side). Then click on the Trigger button, but now choose New/Edit to create a new schedule for your pipeline (package). For this example I choose Daily at 7:00AM. After adding the new schedule you have to Publish your pipeline (again).

Add Schedule

Note: that it uses UTC time.