Create an Azure-SSIS integration runtime in Azure Data Factory

This article provides steps for provisioning an Azure-SQL Server Integration Services (SSIS) integration runtime (IR) in Azure Data Factory. An Azure-SSIS IR supports:

  • Running packages deployed into the SSIS catalog (SSISDB) hosted by an Azure SQL Database server or a managed instance (Project Deployment Model).
  • Running packages deployed into file systems, file shares, or Azure Files (Package Deployment Model).

After an Azure-SSIS IR is provisioned, you can use familiar tools to deploy and run your packages in Azure. These tools include SQL Server Data Tools, SQL Server Management Studio, and command-line tools like dtinstall, dtutil, and dtexec.

The Provisioning Azure-SSIS IR tutorial shows how to create an Azure-SSIS IR via the Azure portal or the Data Factory app. The tutorial also shows how to optionally use an Azure SQL Database server or managed instance to host SSISDB. This article expands on the tutorial and describes how to do these optional tasks:

  • Use an Azure SQL Database server with virtual network service endpoints or a managed instance with a private endpoint to host SSISDB. As a prerequisite, you need to configure virtual network permissions and settings for your Azure-SSIS IR to join a virtual network.

  • Use Azure Active Directory (Azure AD) authentication with the managed identity for your data factory to connect to an Azure SQL Database server or managed instance. As a prerequisite, you need to add the managed identity for your data factory as a database user who can create an SSISDB instance.

  • Join your Azure-SSIS IR to a virtual network, or configure a self-hosted IR as a proxy for your Azure-SSIS IR to access data on-premises.

This article shows how to provision an Azure-SSIS IR by using the Azure portal, Azure PowerShell, and an Azure Resource Manager template.

Prerequisites

Note

This article has been updated to use the new Azure PowerShell Az module. You can still use the AzureRM module, which will continue to receive bug fixes until at least December 2020. To learn more about the new Az module and AzureRM compatibility, see Introducing the new Azure PowerShell Az module. For Az module installation instructions, see Install Azure PowerShell.

  • Azure subscription. If you don't already have a subscription, you can create a free trial account.

  • Azure SQL Database server or managed instance (optional). If you don't already have a database server, create one in the Azure portal before you get started. Data Factory will in turn create an SSISDB instance on this database server.

    We recommend that you create the database server in the same Azure region as the integration runtime. This configuration lets the integration runtime write execution logs into SSISDB without crossing Azure regions.

    Keep these points in mind:

    • Based on the selected database server, the SSISDB instance can be created on your behalf as a single database, as part of an elastic pool, or in a managed instance. It can be accessible in a public network or by joining a virtual network. For guidance in choosing the type of database server to host SSISDB, see the Compare an Azure SQL Database single database, elastic pool, and managed instance section in this article.

      If you use an Azure SQL Database server with virtual network service endpoints or a managed instance with a private endpoint to host SSISDB, or if you require access to on-premises data without configuring self-hosted IR, you need to join your Azure-SSIS IR to a virtual network. For more information, see Join an Azure-SSIS IR to a virtual network.

    • Confirm that the Allow access to Azure services setting is enabled for the database server. This setting is not applicable when you use an Azure SQL Database server with virtual network service endpoints or a managed instance with a private endpoint to host SSISDB. For more information, see Secure your Azure SQL database. To enable this setting by using PowerShell, see New-AzSqlServerFirewallRule.

    • Add the IP address of the client machine, or a range of IP addresses that includes the IP address of the client machine, to the client IP address list in the firewall settings for the database server. For more information, see Azure SQL Database server-level and database-level firewall rules.

    • You can connect to the database server by using SQL authentication with your server admin credentials, or by using Azure AD authentication with the managed identity for your data factory. For the latter, you need to add the managed identity for your data factory into an Azure AD group with access permissions to the database server. For more information, see Enable Azure AD authentication for an Azure-SSIS IR.

    • Confirm that your database server does not have an SSISDB instance already. The provisioning of an Azure-SSIS IR does not support using an existing SSISDB instance.

  • Azure Resource Manager virtual network (optional). You must have an Azure Resource Manager virtual network if at least one of the following conditions is true:

    • You're hosting SSISDB on an Azure SQL Database server with virtual network service endpoints or a managed instance with a private endpoint.
    • You want to connect to on-premises data stores from SSIS packages running on your Azure-SSIS IR without configuring a self-hosted IR.
  • Azure PowerShell (optional). Follow the instructions in How to install and configure Azure PowerShell, if you want to run a PowerShell script to provision your Azure-SSIS IR.

Regional support

For a list of Azure regions in which Data Factory and an Azure-SSIS IR are available, see Data Factory and SSIS IR availability by region.

Comparison of a SQL Database single database, elastic pool, and managed instance

The following table compares certain features of an Azure SQL Database server and managed instance as they relate to Azure-SSIR IR:

Feature Single database/elastic pool Managed instance
Scheduling The SQL Server Agent is not available.

See Schedule a package execution in a Data Factory pipeline.
The Managed Instance Agent is available.
Authentication You can create an SSISDB instance with a contained database user who represents any Azure AD group with the managed identity of your data factory as a member in the db_owner role.

See Enable Azure AD authentication to create an SSISDB instance on an Azure SQL Database server.
You can create an SSISDB instance with a contained database user who represents the managed identity of your data factory.

See Enable Azure AD authentication to create an SSISDB instance in an Azure SQL Database managed instance.
Service tier When you create an Azure-SSIS IR with your Azure SQL Database server, you can select the service tier for SSISDB. There are multiple service tiers. When you create an Azure-SSIS IR with your managed instance, you can't select the service tier for SSISDB. All databases in your managed instance share the same resource allocated to that instance.
Virtual network Your Azure-SSIS IR can join Azure Resource Manager virtual networks only if you use an Azure SQL Database server with virtual network service endpoints, or if you require access to on-premises data stores without configuring self-hosted IR. Your Azure-SSIS IR can join only Azure Resource Manager virtual networks. The virtual network is required when you don't enable a public endpoint for your managed instance.

If you join your Azure-SSIS IR to the same virtual network as your managed instance, make sure that your Azure-SSIS IR is in a different subnet from your managed instance. If you join your Azure-SSIS IR to a different virtual network from your managed instance, we recommend either a virtual network peering or a network-to-network connection. See Connect your application to an Azure SQL Database managed instance.
Distributed transactions This feature is supported through elastic transactions. Microsoft Distributed Transaction Coordinator (MSDTC) transactions are not supported. If your SSIS packages use MSDTC to coordinate distributed transactions, consider migrating to elastic transactions for Azure SQL Database. For more information, see Distributed transactions across cloud databases. Not supported.

Use the Azure portal to create an integration runtime

In this section, you use the Azure portal, specifically the Data Factory user interface (UI) or app, to create an Azure-SSIS IR.

Create a data factory

To create your data factory via the Azure portal, follow the step-by-step instructions in Create a data factory via the UI. Select Pin to dashboard while doing so, to allow quick access after its creation.

After your data factory is created, open its overview page in the Azure portal. Select the Author & Monitor tile to open its Let's get started page on a separate tab. There, you can continue to create your Azure-SSIS IR.

Provision an Azure-SSIS integration runtime

  1. On the Let's get started page, select the Configure SSIS Integration Runtime tile.

    Configure SSIS Integration Runtime tile

  2. On the General Settings page of Integration Runtime Setup, complete the following steps.

    General settings

    a. For Name, enter the name of your integration runtime.

    b. For Description, enter the description of your integration runtime.

    c. For Location, select the location of your integration runtime. Only supported locations are displayed. We recommend that you select the same location of your database server to host SSISDB.

    d. For Node Size, select the size of the node in your integration runtime cluster. Only supported node sizes are displayed. Select a large node size (scale up) if you want to run many compute-intensive or memory-intensive packages.

    e. For Node Number, select the number of nodes in your integration runtime cluster. Only supported node numbers are displayed. Select a large cluster with many nodes (scale out) if you want to run many packages in parallel.

    f. For Edition/License, select the SQL Server edition for your integration runtime: Standard or Enterprise. Select Enterprise if you want to use advanced features on your integration runtime.

    g. For Save Money, select the Azure Hybrid Benefit option for your integration runtime: Yes or No. Select Yes if you want to bring your own SQL Server license with Software Assurance to benefit from cost savings with hybrid use.

    h. Select Next.

  3. On the SQL Settings page, complete the following steps.

    SQL settings

    a. Select the Create SSIS catalog... check box to choose the deployment model for packages to run on your Azure-SSIS IR. You'll choose either the Project Deployment Model where packages are deployed into SSISDB hosted by your database server, or the Package Deployment Model where packages are deployed into file systems, file shares, or Azure Files.

    If you select the check box, you'll need to bring your own database server to host the SSISDB instance that we'll create and manage on your behalf.

    b. For Subscription, select the Azure subscription that has your database server to host SSISDB.

    c. For Location, select the location of your database server to host SSISDB. We recommend that you select the same location of your integration runtime.

    d. For Catalog Database Server Endpoint, select the endpoint of your database server to host SSISDB.

    Based on the selected database server, the SSISDB instance can be created on your behalf as a single database, as part of an elastic pool, or in a managed instance. It can be accessible in a public network or by joining a virtual network. For guidance in choosing the type of database server to host SSISDB, see the Compare an Azure SQL Database single database, elastic pool, and managed instance section in this article.

    If you select an Azure SQL Database server with virtual network service endpoints or a managed instance with a private endpoint to host SSISDB, or if you require access to on-premises data without configuring self-hosted IR, you need to join your Azure-SSIS IR to a virtual network. For more information, see Join an Azure-SSIS IR to a virtual network.

    e. Select the Use AAD authentication with the managed identity for your ADF check box to choose the authentication method for your database server to host SSISDB. You'll choose either SQL authentication or Azure AD authentication with the managed identity for your data factory.

    If you select the check box, you'll need to add the managed identity for your data factory into an Azure AD group with access permissions to your database server. For more information, see Enable Azure AD authentication for an Azure-SSIS IR.

    f. For Admin Username, enter the SQL authentication username for your database server to host SSISDB.

    g. For Admin Password, enter the SQL authentication password for your database server to host SSISDB.

    h. For Catalog Database Service Tier, select the service tier for your database server to host SSISDB. Select the Basic, Standard, or Premium tier, or select an elastic pool name.

    i. Select Test Connection. If the test is successful, select Next.

  4. On the Advanced Settings page, complete the following steps.

    Advanced settings

    a. For Maximum Parallel Executions Per Node, select the maximum number of packages to run concurrently per node in your integration runtime cluster. Only supported package numbers are displayed. Select a low number if you want to use more than one core to run a single large package that's compute or memory intensive. Select a high number if you want to run one or more small packages in a single core.

    b. For Custom Setup Container SAS URI, optionally enter the shared access signature (SAS) uniform resource identifier (URI) of the Azure Blob storage container where your setup script and its associated files are stored. For more information, see Custom setup for an Azure-SSIS IR.

  5. Select the Select a VNet for your Azure-SSIS Integration Runtime to join and allow ADF to create certain network resources check box to choose whether you want to join your integration runtime to a virtual network.

    Select it if you use an Azure SQL Database server with virtual network service endpoints or a managed instance with a private endpoint to host SSISDB, or if you require access to on-premises data. (That is, you have on-premises data sources or destinations in your SSIS packages, without configuring a self-hosted IR.) For more information, see Join an Azure-SSIS IR to a virtual network.

    If you select the check box, complete the following steps.

    Advanced settings with a virtual network

    a. For Subscription, select the Azure subscription that has your virtual network.

    b. For Location, the same location of your integration runtime is selected.

    c. For Type, select the type of your virtual network: classic or Azure Resource Manager. We recommend that you select an Azure Resource Manager virtual network, because classic virtual networks will be deprecated soon.

    d. For VNet Name, select the name of your virtual network. This virtual network should be the same one used for the Azure SQL Database server with virtual network service endpoints or a managed instance in a virtual network to host SSISDB. Or this virtual network should be the same as the one that's connected to your on-premises network.

    e. For Subnet Name, select the name of subnet for your virtual network. This should be a different subnet from the one used for the managed instance in a virtual network to host SSISDB.

  6. Select the Set up Self-Hosted Integration Runtime as a proxy for your Azure-SSIS Integration Runtime check box to choose whether you want to configure a self-hosted IR as a proxy for your Azure-SSIS IR. For more information, see Set up a self-hosted IR as a proxy.

    If you select the check box, complete the following steps.

    Advanced settings with a self-hosted IR

    a. For Self-Hosted Integration Runtime, select your existing self-hosted IR as a proxy for the Azure-SSIS IR.

    b. For Staging Storage Linked Service, select your existing Azure Blob storage linked service. Or, create a new one for staging.

    c. For Staging Path, specify a blob container in your selected Azure Blob storage account. Or, leave it empty to use a default one for staging.

  7. Select VNet Validation > Next.

  8. On the Summary page, review all provisioning settings, bookmark the recommended documentation links, and select Finish to start the creation of your integration runtime.

    Note

    Excluding any custom setup time, this process should finish within 5 minutes. But it might take 20-30 minutes for the Azure-SSIS IR to join a virtual network.

    If you use SSISDB, the Data Factory service will connect to your database server to prepare SSISDB. It also configures permissions and settings for your virtual network, if specified, and joins your Azure-SSIS IR to the virtual network.

    When you provision an Azure-SSIS IR, Access Redistributable and Azure Feature Pack for SSIS are also installed. These components provide connectivity to Excel files, Access files, and various Azure data sources, in addition to the data sources that built-in components already support. For information about other components that you can install, see Custom setup for an Azure-SSIS IR.

  9. On the Connections tab, switch to Integration Runtimes if needed. Select Refresh to refresh the status.

    Creation status

  10. Use the links in the Actions column to stop/start, edit, or delete the integration runtime. Use the last link to view JSON code for the integration runtime. The edit and delete buttons are enabled only when the IR is stopped.

    Azure SSIS IR actions

Azure SSIS integration runtimes in the portal

  1. In the Azure Data Factory UI, switch to the Edit tab and select Connections. Then switch to the Integration Runtimes tab to view existing integration runtimes in your data factory.

    View existing IRs

  2. Select New to create a new Azure-SSIS IR.

    Integration runtime via menu

  3. In the Integration Runtime Setup window, select Lift-and-shift existing SSIS packages to execute in Azure, and then select Next.

    Specify the type of integration runtime

  4. For the remaining steps to set up an Azure-SSIS IR, see the Provision an Azure SSIS integration runtime section.

Use Azure PowerShell to create an integration runtime

In this section, you use Azure PowerShell to create an Azure-SSIS IR.

Create variables

Copy and paste the following script. Specify values for the variables.

### Azure Data Factory information
# If your input contains a PSH special character like "$", precede it with the escape character "`" - for example, "`$"
$SubscriptionName = "[your Azure subscription name]"
$ResourceGroupName = "[your Azure resource group name]"
# Data factory name - must be globally unique
$DataFactoryName = "[your data factory name]"
# For supported regions, see https://azure.microsoft.com/global-infrastructure/services/?products=data-factory&regions=all
$DataFactoryLocation = "EastUS"

### Azure-SSIS integration runtime information. This is a Data Factory compute resource for running SSIS packages.
$AzureSSISName = "[your Azure-SSIS IR name]"
$AzureSSISDescription = "[your Azure-SSIS IR description]"
# For supported regions, see https://azure.microsoft.com/global-infrastructure/services/?products=data-factory&regions=all
$AzureSSISLocation = "EastUS"
# For supported node sizes, see https://azure.microsoft.com/pricing/details/data-factory/ssis/
$AzureSSISNodeSize = "Standard_D8_v3"
# 1-10 nodes are currently supported
$AzureSSISNodeNumber = 2
# Azure-SSIS IR edition/license info: Standard or Enterprise
$AzureSSISEdition = "Standard" # Standard by default, whereas Enterprise lets you use advanced features on your Azure-SSIS IR
# Azure-SSIS IR hybrid usage info: LicenseIncluded or BasePrice
$AzureSSISLicenseType = "LicenseIncluded" # LicenseIncluded by default, whereas BasePrice lets you bring your own on-premises SQL Server license with Software Assurance to earn cost savings from Azure Hybrid Benefit option
# For a Standard_D1_v2 node, up to 4 parallel executions per node are supported. For other nodes, up to (2 x number of cores) are currently supported.
$AzureSSISMaxParallelExecutionsPerNode = 8
# Custom setup info
$SetupScriptContainerSasUri = "" # OPTIONAL to provide the SAS URI of the blob container where your custom setup script and its associated files are stored
# Virtual network info: classic or Azure Resource Manager
$VnetId = "[your virtual network resource ID or leave it empty]" # REQUIRED if you use an Azure SQL Database server with virtual network service endpoints or a managed instance with a private endpoint, or if you require on-premises data without configuring a self-hosted IR. We recommend an Azure Resource Manager virtual network, because classic virtual networks will be deprecated soon.
$SubnetName = "[your subnet name or leave it empty]" # WARNING: Use the same subnet as the one used for your Azure SQL Database server with virtual network service endpoints, or a different subnet from the one used for your managed instance with a private endpoint

### SSISDB info
$SSISDBServerEndpoint = "[your Azure SQL Database server name.database.windows.net or managed instance name.DNS prefix.database.windows.net or managed instance name.public.DNS prefix.database.windows.net,3342 or leave it empty if you do not use SSISDB]" # WARNING: If you use SSISDB, ensure that there's no existing SSISDB on your database server, so we can prepare and manage one on your behalf
# Authentication info: SQL or Azure AD
$SSISDBServerAdminUserName = "[your server admin username for SQL authentication or leave it empty for Azure AD authentication]"
$SSISDBServerAdminPassword = "[your server admin password for SQL authentication or leave it empty for Azure AD authentication]"
# For the basic pricing tier, specify "Basic," not "B." For standard, premium, and elastic pool tiers, specify "S0," "S1," "S2," "S3," etc. See https://docs.microsoft.com/azure/sql-database/sql-database-resource-limits-database-server.
$SSISDBPricingTier = "[Basic|S0|S1|S2|S3|S4|S6|S7|S9|S12|P1|P2|P4|P6|P11|P15|…|ELASTIC_POOL(name = <elastic_pool_name>) for Azure SQL Database server or leave it empty for managed instance]"

Sign in and select a subscription

Add the following script to sign in and select your Azure subscription.

Connect-AzAccount
Select-AzSubscription -SubscriptionName $SubscriptionName

Validate the connection to database server

Add the following script to validate your Azure SQL Database server or managed instance.

# Validate only if you use SSISDB and you don't use virtual network or Azure AD authentication
if(![string]::IsNullOrEmpty($SSISDBServerEndpoint))
{
    if([string]::IsNullOrEmpty($VnetId) -and [string]::IsNullOrEmpty($SubnetName))
    {
        if(![string]::IsNullOrEmpty($SSISDBServerAdminUserName) -and ![string]::IsNullOrEmpty($SSISDBServerAdminPassword))
        {
            $SSISDBConnectionString = "Data Source=" + $SSISDBServerEndpoint + ";User ID=" + $SSISDBServerAdminUserName + ";Password=" + $SSISDBServerAdminPassword
            $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $SSISDBConnectionString;
            Try
            {
                $sqlConnection.Open();
            }
            Catch [System.Data.SqlClient.SqlException]
            {
                Write-Warning "Cannot connect to your Azure SQL Database server, exception: $_";
                Write-Warning "Please make sure the server you specified has already been created. Do you want to proceed? [Y/N]"
                $yn = Read-Host
                if(!($yn -ieq "Y"))
                {
                    Return;
                }
            }
        }
    }
}

Configure the virtual network

Add the following script to automatically configure virtual network permissions and settings for your Azure-SSIS integration runtime to join.

# Make sure to run this script against the subscription to which the virtual network belongs
if(![string]::IsNullOrEmpty($VnetId) -and ![string]::IsNullOrEmpty($SubnetName))
{
    # Register to the Azure Batch resource provider
    $BatchApplicationId = "ddbf3205-c6bd-46ae-8127-60eb93363864"
    $BatchObjectId = (Get-AzADServicePrincipal -ServicePrincipalName $BatchApplicationId).Id
    Register-AzResourceProvider -ProviderNamespace Microsoft.Batch
    while(!(Get-AzResourceProvider -ProviderNamespace "Microsoft.Batch").RegistrationState.Contains("Registered"))
    {
    Start-Sleep -s 10
    }
    if($VnetId -match "/providers/Microsoft.ClassicNetwork/")
    {
        # Assign the VM contributor role to Microsoft.Batch
        New-AzRoleAssignment -ObjectId $BatchObjectId -RoleDefinitionName "Classic Virtual Machine Contributor" -Scope $VnetId
    }
}

Create a resource group

Create an Azure resource group by using the New-AzResourceGroup command. A resource group is a logical container into which Azure resources are deployed and managed as a group.

If your resource group already exists, don't copy this code to your script.

New-AzResourceGroup -Location $DataFactoryLocation -Name $ResourceGroupName

Create a data factory

Run the following command to create a data factory.

Set-AzDataFactoryV2 -ResourceGroupName $ResourceGroupName `
                         -Location $DataFactoryLocation `
                         -Name $DataFactoryName

Create an integration runtime

Run the following commands to create an Azure-SSIS integration runtime that runs SSIS packages in Azure.

If you don't use SSISDB, you can omit the CatalogServerEndpoint, CatalogPricingTier, and CatalogAdminCredential parameters.

If you don't use an Azure SQL Database server with virtual network service endpoints or a managed instance with a private endpoint to host SSISDB, or if you require access to on-premises data, you can omit the VNetId and Subnet parameters or pass empty values for them. You can also omit them if you configure self-hosted IR as a proxy for your Azure-SSIS IR to access data on-premises. Otherwise, you can't omit them and must pass valid values from your virtual network configuration. For more information, see Join an Azure-SSIS IR to a virtual network.

If you use managed instance to host SSISDB, you can omit the CatalogPricingTier parameter or pass an empty value for it. Otherwise, you can't omit it and must pass a valid value from the list of supported pricing tiers for Azure SQL Database. For more information, see SQL Database resource limits.

If you use Azure AD authentication with the managed identity for your data factory to connect to the database server, you can omit the CatalogAdminCredential parameter. But you must add the managed identity for your data factory into an Azure AD group with access permissions to the database server. For more information, see Enable Azure AD authentication for an Azure-SSIS IR. Otherwise, you can't omit it and must pass a valid object formed from your server admin username and password for SQL authentication.

Set-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
                                           -DataFactoryName $DataFactoryName `
                                           -Name $AzureSSISName `
                                           -Description $AzureSSISDescription `
                                           -Type Managed `
                                           -Location $AzureSSISLocation `
                                           -NodeSize $AzureSSISNodeSize `
                                           -NodeCount $AzureSSISNodeNumber `
                                           -Edition $AzureSSISEdition `
                                           -LicenseType $AzureSSISLicenseType `
                                           -MaxParallelExecutionsPerNode $AzureSSISMaxParallelExecutionsPerNode `
                                           -VnetId $VnetId `
                                           -Subnet $SubnetName
	   
# Add the CatalogServerEndpoint, CatalogPricingTier, and CatalogAdminCredential parameters if you use SSISDB
if(![string]::IsNullOrEmpty($SSISDBServerEndpoint))
{
    Set-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
                                               -DataFactoryName $DataFactoryName `
                                               -Name $AzureSSISName `
                                               -CatalogServerEndpoint $SSISDBServerEndpoint `
                                               -CatalogPricingTier $SSISDBPricingTier

    if(![string]::IsNullOrEmpty($SSISDBServerAdminUserName) –and ![string]::IsNullOrEmpty($SSISDBServerAdminPassword)) # Add the CatalogAdminCredential parameter if you don't use Azure AD authentication
    {
        $secpasswd = ConvertTo-SecureString $SSISDBServerAdminPassword -AsPlainText -Force
        $serverCreds = New-Object System.Management.Automation.PSCredential($SSISDBServerAdminUserName, $secpasswd)

        Set-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
                                                   -DataFactoryName $DataFactoryName `
                                                   -Name $AzureSSISName `
                                                   -CatalogAdminCredential $serverCreds
    }
}

# Add the SetupScriptContainerSasUri parameter if you use custom setup
if(![string]::IsNullOrEmpty($SetupScriptContainerSasUri))
{
    Set-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
                                               -DataFactoryName $DataFactoryName `
                                               -Name $AzureSSISName `
                                               -SetupScriptContainerSasUri $SetupScriptContainerSasUri
}

Start the integration runtime

Run the following commands to start the Azure-SSIS integration runtime.

write-host("##### Starting #####")
Start-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
                                             -DataFactoryName $DataFactoryName `
                                             -Name $AzureSSISName `
                                             -Force

write-host("##### Completed #####")
write-host("If any cmdlet is unsuccessful, please consider using -Debug option for diagnostics.")

Note

Excluding any custom setup time, this process should finish within 5 minutes. But it might take 20-30 minutes for the Azure-SSIS IR to join a virtual network.

If you use SSISDB, the Data Factory service will connect to your database server to prepare SSISDB. It also configures permissions and settings for your virtual network, if specified, and joins your Azure-SSIS IR to the virtual network.

When you provision an Azure-SSIS IR, Access Redistributable and Azure Feature Pack for SSIS are also installed. These components provide connectivity to Excel files, Access files, and various Azure data sources, in addition to the data sources that built-in components already support. For information about other components that you can install, see Custom setup for an Azure-SSIS IR.

Full script

Here's the full script that creates an Azure-SSIS integration runtime.

### Azure Data Factory information
# If your input contains a PSH special character like "$", precede it with the escape character "`" - for example, "`$"
$SubscriptionName = "[your Azure subscription name]"
$ResourceGroupName = "[your Azure resource group name]"
# Data factory name - must be globally unique
$DataFactoryName = "[your data factory name]"
# For supported regions, see https://azure.microsoft.com/global-infrastructure/services/?products=data-factory&regions=all
$DataFactoryLocation = "EastUS"

### Azure-SSIS integration runtime information. This is a Data Factory compute resource for running SSIS packages.
$AzureSSISName = "[your Azure-SSIS IR name]"
$AzureSSISDescription = "[your Azure-SSIS IR description]"
# For supported regions, see https://azure.microsoft.com/global-infrastructure/services/?products=data-factory&regions=all
$AzureSSISLocation = "EastUS"
# For supported node sizes, see https://azure.microsoft.com/pricing/details/data-factory/ssis/
$AzureSSISNodeSize = "Standard_D8_v3"
# 1-10 nodes are currently supported
$AzureSSISNodeNumber = 2
# Azure-SSIS IR edition/license info: Standard or Enterprise
$AzureSSISEdition = "Standard" # Standard by default, whereas Enterprise lets you use advanced features on your Azure-SSIS IR
# Azure-SSIS IR hybrid usage info: LicenseIncluded or BasePrice
$AzureSSISLicenseType = "LicenseIncluded" # LicenseIncluded by default, whereas BasePrice lets you bring your own on-premises SQL Server license with Software Assurance to earn cost savings from the Azure Hybrid Benefit option
# For a Standard_D1_v2 node, up to four parallel executions per node are supported. For other nodes, up to (2 x number of cores) are currently supported.
$AzureSSISMaxParallelExecutionsPerNode = 8
# Custom setup info
$SetupScriptContainerSasUri = "" # OPTIONAL to provide the SAS URI of the blob container where your custom setup script and its associated files are stored
# Virtual network info: classic or Azure Resource Manager
$VnetId = "[your virtual network resource ID or leave it empty]" # REQUIRED if you use an Azure SQL Database server with virtual network service endpoints or a managed instance with a private endpoint, or if you require on-premises data without configuring a self-hosted IR. We recommend an Azure Resource Manager virtual network, because classic virtual networks will be deprecated soon.
$SubnetName = "[your subnet name or leave it empty]" # WARNING: Use the same subnet as the one used for your Azure SQL Database server with virtual network service endpoints, or a different subnet from the one used for your managed instance with a private endpoint

### SSISDB info
$SSISDBServerEndpoint = "[your Azure SQL Database server name.database.windows.net or managed instance name.DNS prefix.database.windows.net or managed instance name.public.DNS prefix.database.windows.net,3342 or leave it empty if you do not use SSISDB]" # WARNING: If you use SSISDB, ensure that there's no existing SSISDB on your database server, so we can prepare and manage one on your behalf
# Authentication info: SQL or Azure AD
$SSISDBServerAdminUserName = "[your server admin username for SQL authentication or leave it empty for Azure AD authentication]"
$SSISDBServerAdminPassword = "[your server admin password for SQL authentication or leave it empty for Azure AD authentication]"
# For the basic pricing tier, specify "Basic," not "B." For standard, premium, and elastic pool tiers, specify "S0," "S1," "S2," "S3," etc. See https://docs.microsoft.com/azure/sql-database/sql-database-resource-limits-database-server.
$SSISDBPricingTier = "[Basic|S0|S1|S2|S3|S4|S6|S7|S9|S12|P1|P2|P4|P6|P11|P15|…|ELASTIC_POOL(name = <elastic_pool_name>) for Azure SQL Database server or leave it empty for managed instance]"

### Sign in and select a subscription
Connect-AzAccount
Select-AzSubscription -SubscriptionName $SubscriptionName

### Validate the connection to the database server
# Validate only if you use SSISDB and don't use a virtual network or Azure AD authentication
if(![string]::IsNullOrEmpty($SSISDBServerEndpoint))
{
    if([string]::IsNullOrEmpty($VnetId) -and [string]::IsNullOrEmpty($SubnetName))
    {
        if(![string]::IsNullOrEmpty($SSISDBServerAdminUserName) -and ![string]::IsNullOrEmpty($SSISDBServerAdminPassword))
        {
            $SSISDBConnectionString = "Data Source=" + $SSISDBServerEndpoint + ";User ID=" + $SSISDBServerAdminUserName + ";Password=" + $SSISDBServerAdminPassword
            $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $SSISDBConnectionString;
            Try
            {
                $sqlConnection.Open();
            }
            Catch [System.Data.SqlClient.SqlException]
            {
                Write-Warning "Cannot connect to your Azure SQL Database server, exception: $_";
                Write-Warning "Please make sure the server you specified has already been created. Do you want to proceed? [Y/N]"
                $yn = Read-Host
                if(!($yn -ieq "Y"))
                {
                    Return;
                }
            }
        }
    }
}

### Configure a virtual network
# Make sure to run this script against the subscription to which the virtual network belongs
if(![string]::IsNullOrEmpty($VnetId) -and ![string]::IsNullOrEmpty($SubnetName))
{
    # Register to the Azure Batch resource provider
    $BatchApplicationId = "ddbf3205-c6bd-46ae-8127-60eb93363864"
    $BatchObjectId = (Get-AzADServicePrincipal -ServicePrincipalName $BatchApplicationId).Id
    Register-AzResourceProvider -ProviderNamespace Microsoft.Batch
    while(!(Get-AzResourceProvider -ProviderNamespace "Microsoft.Batch").RegistrationState.Contains("Registered"))
    {
    Start-Sleep -s 10
    }
    if($VnetId -match "/providers/Microsoft.ClassicNetwork/")
    {
        # Assign the VM contributor role to Microsoft.Batch
        New-AzRoleAssignment -ObjectId $BatchObjectId -RoleDefinitionName "Classic Virtual Machine Contributor" -Scope $VnetId
    }
}

### Create a data factory
Set-AzDataFactoryV2 -ResourceGroupName $ResourceGroupName `
                         -Location $DataFactoryLocation `
                         -Name $DataFactoryName

### Create an integration runtime
Set-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
                                           -DataFactoryName $DataFactoryName `
                                           -Name $AzureSSISName `
                                           -Description $AzureSSISDescription `
                                           -Type Managed `
                                           -Location $AzureSSISLocation `
                                           -NodeSize $AzureSSISNodeSize `
                                           -NodeCount $AzureSSISNodeNumber `
                                           -Edition $AzureSSISEdition `
                                           -LicenseType $AzureSSISLicenseType `
                                           -MaxParallelExecutionsPerNode $AzureSSISMaxParallelExecutionsPerNode `
                                           -VnetId $VnetId `
                                           -Subnet $SubnetName
	   
# Add CatalogServerEndpoint, CatalogPricingTier, and CatalogAdminCredential parameters if you use SSISDB
if(![string]::IsNullOrEmpty($SSISDBServerEndpoint))
{
    Set-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
                                               -DataFactoryName $DataFactoryName `
                                               -Name $AzureSSISName `
                                               -CatalogServerEndpoint $SSISDBServerEndpoint `
                                               -CatalogPricingTier $SSISDBPricingTier

    if(![string]::IsNullOrEmpty($SSISDBServerAdminUserName) –and ![string]::IsNullOrEmpty($SSISDBServerAdminPassword)) # Add the CatalogAdminCredential parameter if you don't use Azure AD authentication
    {
        $secpasswd = ConvertTo-SecureString $SSISDBServerAdminPassword -AsPlainText -Force
        $serverCreds = New-Object System.Management.Automation.PSCredential($SSISDBServerAdminUserName, $secpasswd)

        Set-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
                                                   -DataFactoryName $DataFactoryName `
                                                   -Name $AzureSSISName `
                                                   -CatalogAdminCredential $serverCreds
    }
}

# Add the SetupScriptContainerSasUri parameter when you use custom setup
if(![string]::IsNullOrEmpty($SetupScriptContainerSasUri))
{
    Set-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
                                               -DataFactoryName $DataFactoryName `
                                               -Name $AzureSSISName `
                                               -SetupScriptContainerSasUri $SetupScriptContainerSasUri
}

### Start the integration runtime
write-host("##### Starting #####")
Start-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
                                             -DataFactoryName $DataFactoryName `
                                             -Name $AzureSSISName `
                                             -Force

write-host("##### Completed #####")
write-host("If any cmdlet is unsuccessful, please consider using -Debug option for diagnostics.")

Use an Azure Resource Manager template to create an integration runtime

In this section, you use an Azure Resource Manager template to create the Azure-SSIS integration runtime. Here's a sample walkthrough:

  1. Create a JSON file with the following Azure Resource Manager template. Replace values in the angle brackets (placeholders) with your own values.

    {
      "contentVersion": "1.0.0.0",
        "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
        "parameters": {},
        "variables": {},
        "resources": [{
            "name": "<Specify a name for your data factory>",
            "apiVersion": "2018-06-01",
            "type": "Microsoft.DataFactory/factories",
            "location": "East US",
            "properties": {},
            "resources": [{
                "type": "integrationruntimes",
                "name": "<Specify a name for your Azure-SSIS IR>",
                "dependsOn": [ "<The name of the data factory you specified at the beginning>" ],
                "apiVersion": "2018-06-01",
                "properties": {
                    "type": "Managed",
                    "typeProperties": {
                        "computeProperties": {
                            "location": "East US",
                            "nodeSize": "Standard_D8_v3",
                            "numberOfNodes": 1,
                            "maxParallelExecutionsPerNode": 8
                        },
                        "ssisProperties": {
                            "catalogInfo": {
                                "catalogServerEndpoint": "<Azure SQL Database server name>.database.windows.net",
                                "catalogAdminUserName": "<Azure SQL Database server admin username>",
                                "catalogAdminPassword": {
                                    "type": "SecureString",
                                    "value": "<Azure SQL Database server admin password>"
                                },
                                "catalogPricingTier": "Basic"
                            }
                        }
                    }
                }
            }]
        }]
    }
    
  2. To deploy the Azure Resource Manager template, run the New-AzResourceGroupDeployment command as shown in the following example. In the example, ADFTutorialResourceGroup is the name of your resource group. ADFTutorialARM.json is the file that contains the JSON definition for your data factory and the Azure-SSIS IR.

    New-AzResourceGroupDeployment -Name MyARMDeployment -ResourceGroupName ADFTutorialResourceGroup -TemplateFile ADFTutorialARM.json
    

    This command creates your data factory and Azure-SSIS IR in it, but it doesn't start the IR.

  3. To start your Azure-SSIS IR, run the Start-AzDataFactoryV2IntegrationRuntime command:

    Start-AzDataFactoryV2IntegrationRuntime -ResourceGroupName "<Resource Group Name>" `
                                                 -DataFactoryName "<Data Factory Name>" `
                                                 -Name "<Azure SSIS IR Name>" `
                                                 -Force
    

Note

Excluding any custom setup time, this process should finish within 5 minutes. But it might take 20-30 minutes for the Azure-SSIS IR to join a virtual network.

If you use SSISDB, the Data Factory service will connect to your database server to prepare SSISDB. It also configures permissions and settings for your virtual network, if specified, and joins your Azure-SSIS IR to the virtual network.

When you provision an Azure-SSIS IR, Access Redistributable and Azure Feature Pack for SSIS are also installed. These components provide connectivity to Excel files, Access files, and various Azure data sources, in addition to the data sources that built-in components already support. For information about other components that you can install, see Custom setup for an Azure-SSIS IR.

Deploy SSIS packages

If you use SSISDB, you can deploy your packages into it and run them on the Azure-SSIS IR by using SQL Server Data Tools or SQL Server Management Studio tools. These tools connect to your database server via its server endpoint:

  • For an Azure SQL Database server with a private endpoint, the server endpoint format is <server name>.database.windows.net.
  • For a managed instance with a private endpoint, the server endpoint format is <server name>.<dns prefix>.database.windows.net.
  • For a managed instance with a public endpoint, the server endpoint format is <server name>.public.<dns prefix>.database.windows.net,3342.

If you don't use SSISDB, you can deploy your packages into file systems, file shares, or Azure Files. You can then run them on the Azure-SSIS IR by using the dtinstall, dtutil, and dtexec command-line tools. For more information, see Deploy SSIS packages.

In both cases, you can also run your deployed packages on the Azure-SSIS IR by using the Execute SSIS Package activity in Data Factory pipelines. For more information, see Invoke SSIS package execution as a first-class Data Factory activity.

Next steps

See other Azure-SSIS IR topics in this documentation: