Picture of teitelberg


Azure SSIS IR Installation (PowerShell)

To use the component with the Azure SSIS Integration Runtime, the component must be installed and configured on the corresponding Azure nodes. The actual installation and licensing of the component takes place via a batch file, which is automatically executed when the Azure SSIS IR is started.

The batch file or the location of the batch file must be defined when creating the Azure SSIS IR.

The following script explains the complete creation of a Data Factory with Azure SSIS IR instance via PowerShell.

First, we declare various variables for the PowerShell script. Please note that you must define all values <VARIABLE VALUE> with your data.

# Subscription variables
$SubscriptionName = "<YOUR SUBSCRIPTIONNAME>"
$ResourceGroupName = "<YOUR RESOURCE GROUP NAME>"
# The name of the Azure data factory must be globally unique.
$DataFactoryName = "<DATA FACTORY NAME>"
# Currently, Data Factory version 2 allows you to create data factories only in 
# the East US, East US2, and West Europe regions.
$Location = "East US"
# Data Factory / SSIS IR variables
$AzureSSISNodeSize = "Standard_D1_v2"
$AzureSSISNodeNumber = 1
$AzureSSISMaxParallelExecutionsPerNode = 1
$AzureSSISDescription = "Azure SSIS IR for SQLPhonetics.NET"
#SQLPhonetics.NET Setup-Script
$MySetupScriptContainerSasUri = "<YOUR SETUP SCRIPT CONTAINER>"
# SSISDB/Server
$Servername = "<SERVERNAME>"
$SSISDBServerAdminUserName = "<USERNAME>"
$SSISDBServerAdminPassword = "<PASSWORD>"
$SSISDBServerEndpoint = $Servername + ".database.windows.net"
$SSISDBPricingTier = "Basic"
# SQL Server Connection
$SSISDBConnectionString = "Data Source=" + $SSISDBServerEndpoint + ";User ID=" + $SSISDBServerAdminUserName +";Password="+ $SSISDBServerAdminPassword; 

The following script logs you in to your defined subscription, creates a Data Factory V2 and an Azure SSIS IR and then starts the Azure SSIS IR.

# Log in to the defined Azure Subscription
Login-AzureRmAccount -Subscription $SubscriptionName
# Creating the Azure Data Factory V2
# If you want to use an existing Data Factory V2, you can omit this step
Set-AzureRmDataFactoryV2 -ResourceGroupName $ResourceGroupName `
                         -Location $Location `
                         -Name $DataFactoryName 
# Definition of the Azure SSIS IR
# For the installation of SQLPhonetics.NET components, the definition of the 
# SetupScriptContainerSasUri parameter is particularly important. This makes 
# the Azure SSIS IR aware of the setup script, which is executed every time 
# the Azure SSIS IR is started.
Set-AzureRmDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
                                           -DataFactoryName $DataFactoryName `
                                           -Name $AzureSSISName `
                                           -SetupScriptContainerSasUri $MySetupScriptContainerSasUri `
                                           -Type Managed `
                                           -CatalogServerEndpoint $SSISDBServerEndpoint `
                                           -CatalogAdminCredential $serverCreds `
                                           -CatalogPricingTier $SSISDBPricingTier `
                                           -Description $AzureSSISDescription `
                                           -Location $Location `
                                           -NodeSize $AzureSSISNodeSize `
                                           -NodeCount $AzureSSISNodeNumber `
                                           -MaxParallelExecutionsPerNode $AzureSSISMaxParallelExecutionsPerNode
# Start the integrated service
# Starting the Azure SSIS IR can take up to 20 minutes
Start-AzureRmDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
    -DataFactoryName $DataFactoryName `
    -Name $AzureSSISName `

The batch file to be executed by the Azure SSIS IR during the boot process contains various SQLPhonetics.NET specific settings. If you want to use another component in Azure SSIS IR, refer to the corresponding manufacturer-specific parameters.

The batch file first runs the installer of SQLPhonetics.NET via msiexec. To do this, the MSI file together with the batch file must be on the same blob storage that you previously defined using the SetupScriptContainerSasUri parameter.

Then the license system of the SQLPhonetics.NET component is called. The license ID and password you received when you purchased the component are transferred here as parameters. As an additional parameter, you must pass the name of a blob container and the connection string to a blob storage to the component. On this container the license system stores the license of the component for your created Azure SSIS IR. If you use several nodes or stop and restart the component several times, the existing license is accessed.

msiexec /i oh22is.match.setup.msi /qn 
"C:\Program Files (x86)\oh22information services GmbH\SQLPhonetics.NET for SSIS\oh22is.Licensing.exe" "<LICENSE ID>" "<PASSWORD>" "<CONTAINER>" "<BLOB STORAGE CONNECTION STRING>"

Make sure that the number of licenses used corresponds to the maximum size of your Azure SSIS IR.