How to provision ADF with Azure SSIS IR and COZYROC SSIS+

COZYROC SSIS+ can be easily used in Azure SSIS Integration Runtime, which can be provisioned in the context of Azure Data Factory or Azure Synapse Analytics. The installation of COZYROC SSIS+ in the Azure SSIS IR is quite straightforward: one puts all installation files for the custom components pack (e.g. .msi files), along with a command file with the fixed name main.cmd in an Azure Storage Blob container, which is then referenced in the Azure SSIS IR creation process with a Shared Access Signature URI. During each IR node's instantiation, this blob container's content is copied inside node, and the main.cmd file executed. The exact steps are outlined in this KB article. That's the most common way to deploy COZYROC SSIS+ in Azure SSIS IR.

However, if you would also like to provision an Integration Runtime in an automated manner by utilizing the automation capabilities of Azure Data Factory via PowerShell, we have prepared set of simple commandlets and bundled them in a PowerShell module SSIS-Plus-Azure.zip. Please check below the tutorial below:

Provisioning the Integration Runtime

A few simple steps are required for provisioning the Azure SSIS Integration Runtime with the full COZYROC SSIS+ components pack.

Step 1: Optionally create an Azure SQL Database/Managed Instance for the catalog (Azure SSIS IR supports also package deployment)

The Azure SSIS Integration Runtime expects an existing SQL Database to serve as a package catalog, as described in the Azure SSIS IR creation tutorial from Microsoft. Provide the credentials in the configuration file, as illustrated in Step 4.

Step 2: Create an Azure Storage Blob container for installation files

Using either the free Microsoft Azure Storage Explorer tool, or the Azure web portal. Then, put the name of this container in the configuration file, as described in Step 4.

Step 3: Install Azure PowerShell

See how here

Step 4: Encapsulate your configuration in a JSON file

Include all parameters needed for performing the whole process: creating a data factory, verifying the connection to the DB (from step 1) and creating the IR. The file looks like this:

All properties are related to the actual IR creation. These are loaded like this:

Step 5: Publish required files in the Azure Storage Blob container

This can be done manually with the proper Microsoft tools, or using the PSH commandlets we provide, like this:

The latter commandlet uploads the latest SSIS+ Installer. The other file which is needed is main.cmd which gets executed as part of the IR provisioning. Because it has both common code for installing COZYROC's SSIS+ and custom lines dependent on the username, we've provided an easy way to fill in the LICENSEE information. Please replace the LICENSEE variable value in the batch file with your Licensee identifier. Keep in mind that an intermediate main.cmd is created.

The contents of main.cmd should look like this (where LICENSEE value is replaced with the Licensee identifier) :

Step 6: Provision the Integration Runtime

This can be achieved with a single command:

Ready! The Data Factory and the Integration runtime are created. If one already has ADF created and just wants to provision a new IR, this is easily done - just omit the -CreateDF switch.

And, creating an IR with the same parameters, but a different name is also very easy:

Step 7: Start/Stop the Integration Runtime

Once the IR is provisioned the only step left is to start it. Keep in mind that it takes time - between 20 and 30 minutes.

Stopping is just as easy, but much quicker:

Caveats and troubleshooting

There is no guarantee that this easy provisioning of Azure SSIS IR will not have problems and, unfortunately, the error messages are not extremely helpful. The good news is that both the standard output and the error streams are captured during main.cmd execution, and the logs can be inspected later.

They can be found in the provided Blob container, in the folder named main.cmd.log, which has a corresponding sub-folder for each of the nodes. Each of these per-node folders will have four files: stderr.log, stdout.log, install.log and license.log. However, if some of the steps in the installation script fail, then the IR will not be started and no log files are provided. There will be no trace of the attempted IR node instantiation.

SSIS package deployment and execution

After an Azure SSIS Integration Runtime instance is started, deploying and running a package is very straightforward. Actually, it is no different from running an SSIS package from a catalog in an on-premises SQL Server. One must provide the Azure SQL Database's credentials for deploying the package, and later, using SQL Server Management Studio, for example, connect to this SQL Database instance and trigger the package execution.

The Azure SSIS Integration Runtime can access other web services (e.g. common REST APIs), but, of course, getting access to some on-premises data needs additional VNet setup, like the example given here for Point-to-Site VNet configuration.