Provision Enterprise Edition for the Azure-SSIS Integration Runtime

APPLIES TO: yesAzure Data Factory yesAzure Synapse Analytics (Preview)

The Enterprise Edition of the Azure-SSIS Integration Runtime lets you use the following advanced and premium features:

  • Change Data Capture (CDC) components
  • Oracle, Teradata, and SAP BW connectors
  • SQL Server Analysis Services (SSAS) and Azure Analysis Services (AAS) connectors and transformations
  • Fuzzy Grouping and Fuzzy Lookup transformations
  • Term Extraction and Term Lookup transformations

Some of these features require you to install additional components to customize the Azure-SSIS IR. For more info about how to install additional components, see Custom setup for the Azure-SSIS integration runtime.

Enterprise features

Enterprise FeaturesDescriptions
CDC componentsThe CDC Source, Control Task, and Splitter Transformation are preinstalled on the Azure-SSIS IR Enterprise Edition. To connect to Oracle, you also need to install the CDC Designer and Service on another computer.
Oracle connectorsThe Oracle Connection Manager, Source, and Destination are preinstalled on the Azure-SSIS IR Enterprise Edition. You also need to install the Oracle Call Interface (OCI) driver, and if necessary configure the Oracle Transport Network Substrate (TNS), on the Azure-SSIS IR. For more info, see Custom setup for the Azure-SSIS integration runtime.
Teradata connectorsYou need to install the Teradata Connection Manager, Source, and Destination, as well as the Teradata Parallel Transporter (TPT) API and Teradata ODBC driver, on the Azure-SSIS IR Enterprise Edition. For more info, see Custom setup for the Azure-SSIS integration runtime.
SAP BW connectorsThe SAP BW Connection Manager, Source, and Destination are preinstalled on the Azure-SSIS IR Enterprise Edition. You also need to install the SAP BW driver on the Azure-SSIS IR. These connectors support SAP BW 7.0 or earlier versions. To connect to later versions of SAP BW or other SAP products, you can purchase and install SAP connectors from third-party ISVs on the Azure-SSIS IR. For more info about how to install additional components, see Custom setup for the Azure-SSIS integration runtime.
Analysis Services componentsThe Data Mining Model Training Destination, the Dimension Processing Destination, and the Partition Processing Destination, as well as the Data Mining Query Transformation, are preinstalled on the Azure-SSIS IR Enterprise Edition. All these components support SQL Server Analysis Services (SSAS), but only the Partition Processing Destination supports Azure Analysis Services (AAS). To connect to SSAS, you also need to configure Windows Authentication credentials in SSISDB. In addition to these components, the Analysis Services Execute DDL Task, the Analysis Services Processing Task, and the Data Mining Query Task are also preinstalled on the Azure-SSIS IR Standard/Enterprise Edition.
Fuzzy Grouping and Fuzzy Lookup transformationsThe Fuzzy Grouping and Fuzzy Lookup transformations are preinstalled on the Azure-SSIS IR Enterprise Edition. These components support both SQL Server and Azure SQL Database for storing reference data.
Term Extraction and Term Lookup transformationsThe Term Extraction and Term Lookup transformations are preinstalled on the Azure-SSIS IR Enterprise Edition. These components support both SQL Server and Azure SQL Database for storing reference data.

Instructions

 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.

  1. Download and install Azure PowerShell.
  2. When you provision or reconfigure the Azure-SSIS IR with PowerShell, run Set-AzDataFactoryV2IntegrationRuntime with Enterprise as the value for the Edition parameter before you start the Azure-SSIS IR. Here is a sample script:PowerShellCopy$MyAzureSsisIrEdition = "Enterprise" Set-AzDataFactoryV2IntegrationRuntime -DataFactoryName $MyDataFactoryName -Name $MyAzureSsisIrName -ResourceGroupName $MyResourceGroupName -Edition $MyAzureSsisIrEdition Start-AzDataFactoryV2IntegrationRuntime -DataFactoryName $MyDataFactoryName -Name $MyAzureSsisIrName -ResourceGroupName $MyResourceGroupName

Next steps

Provision the Azure-SSIS integration runtime in Azure Data Factory

APPLIES TO: yesAzure Data Factory noAzure Synapse Analytics (Preview)

This tutorial provides steps for using the Azure portal to provision 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 (SSDT), SQL Server Management Studio (SSMS), and command-line tools like dtinstalldtutil, and dtexec.

For conceptual information on Azure-SSIS IRs, see Azure-SSIS integration runtime overview.

In this tutorial, you complete the following steps:

  • Create a data factory.
  • Provision an Azure-SSIS integration runtime.

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 have an Azure subscription, create a free account before you begin.
  • Azure SQL Database server (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 Compare an Azure SQL Database single database, elastic pool, and managed instance.If you use an Azure SQL Database server with IP firewall rules/virtual network service endpoints or a managed instance with private endpoint to host SSISDB, or if you require access to on-premises data without configuring a self-hosted IR, you need to join your Azure-SSIS IR to a virtual network. For more information, see Create an Azure-SSIS IR in 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 IP firewall rules/virtual network service endpoints or a managed instance with 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 Create an Azure-SSIS IR with Azure AD authentication.
    • 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.

 Note

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

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 the Let’s get started page on a separate tab. There, you can continue to create your Azure-SSIS IR.

Create an Azure-SSIS integration runtime

From the Data Factory overview

  1. On the Let’s get started page, select the Configure SSIS Integration Runtime tile."Configure SSIS Integration Runtime" tile
  2. For the remaining steps to set up an Azure-SSIS IR, see the Provision an Azure-SSIS integration runtime section.

From the authoring UI

  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.Selections for viewing existing IRs
  2. Select New to create an Azure-SSIS IR.Integration runtime via menu
  3. In the Integration Runtime Setup panel, select the Lift-and-shift existing SSIS packages to execute in Azure tile, 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.

Provision an Azure-SSIS integration runtime

  1. On the General Settings section of Integration Runtime Setup panel, complete the following steps.General settings
    1. For Name, enter the name of your integration runtime.
    2. For Description, enter the description of your integration runtime.
    3. 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.
    4. For Node Size, select the size of 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.
    5. 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.
    6. 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.
    7. 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.
    8. Select Next.
  2. On the SQL Settings section, complete the following steps.SQL settings
    1. Select the Create SSIS catalog (SSISDB) hosted by Azure SQL Database server/Managed Instance to store your projects/packages/environments/execution logs 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 SSISDB that we’ll create and manage on your behalf.
      1. For Subscription, select the Azure subscription that has your database server to host SSISDB.
      2. For Location, select the location of your database server to host SSISDB. We recommend that you select the same location of your integration runtime.
      3. 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 Compare an Azure SQL Database single database, elastic pool, and managed instance.If you select an Azure SQL Database server with IP firewall rules/virtual network service endpoints or a managed instance with private endpoint to host SSISDB, or if you require access to on-premises data without configuring a self-hosted IR, you need to join your Azure-SSIS IR to a virtual network. For more information, see Create an Azure-SSIS IR in a virtual network.
      4. 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 Create an Azure-SSIS IR with Azure AD authentication.
      5. For Admin Username, enter the SQL authentication username for your database server to host SSISDB.
      6. For Admin Password, enter the SQL authentication password for your database server to host SSISDB.
      7. 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.
      8. Select Test Connection. If the test is successful, select Next.
  3. On the Advanced Settings section, complete the following steps.Advanced settings
    1. 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.
    2. Select the Customize your Azure-SSIS Integration Runtime with additional system configurations/component installations check box to choose whether you want to add standard/express custom setups on your Azure-SSIS IR. For more information, see Custom setup for an Azure-SSIS IR.
    3. Select the Select a VNet for your Azure-SSIS Integration Runtime to join, allow ADF to create certain network resources, and optionally bring your own static public IP addresses check box to choose whether you want to join your Azure-SSIS IR to a virtual network.Select it if you use an Azure SQL Database server with IP firewall rules/virtual network service endpoints or a managed instance with private endpoint to host SSISDB, or if you require access to on-premises data without configuring a self-hosted IR. For more information, see Create an Azure-SSIS IR in a virtual network.
    4. 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 proxy for your Azure-SSIS IR. For more information, see Set up a self-hosted IR as proxy.
    5. Select Continue.
  4. On the Summary section, review all provisioning settings, bookmark the recommended documentation links, and select Finish to start the creation of your integration runtime. NoteExcluding any custom setup time, this process should finish within 5 minutes.If you use SSISDB, the Data Factory service will connect to your database server to prepare SSISDB.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.
  5. On the Connections tab, switch to Integration Runtimes if needed. Select Refresh to refresh the status.Creation status, with "Refresh" button
  6. 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.Links in the "Actions" column

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 (SSDT) or SQL Server Management Studio (SSMS) tools. These tools connect to your database server via its server endpoint:

  • For an Azure SQL Database server, the server endpoint format is <server name>.database.windows.net.
  • For a managed instance with private endpoint, the server endpoint format is <server name>.<dns prefix>.database.windows.net.
  • For a managed instance with 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 and run them on the Azure-SSIS IR by using the dtinstalldtutil, and dtexec command-line tools. For more information, see Deploy SSIS packages.

In both cases, you can also run your deployed packages on 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.

Set up an Azure-SSIS IR in Azure Data Factory by using PowerShell

APPLIES TO: yesAzure Data Factory noAzure Synapse Analytics (Preview)

This tutorial shows you how to up an Azure-SQL Server Integration Services Integration Runtime (Azure-SSIS IR) in Azure Data Factory. An Azure-SSIS IR supports running packages that are deployed to:

  • An SSIS catalog (SSISDB) that’s hosted by an Azure SQL Database server instance or a managed instance (the project deployment model).
  • File systems, file shares, or an Azure Files share (the package deployment model).

After the Azure-SSIS IR is set up, you can use familiar tools, such as SQL Server Data Tools (SSDT) and SQL Server Management Studio (SSMS), to deploy and run your packages in Azure. You can also use command-line utilities, such as dtinstalldtutil, and dtexec.

 Note

This article demonstrates using Azure PowerShell to set up an Azure-SSIS IR. To use the Azure portal or an Azure Data Factory app to set up the Azure-SSIS IR, see Tutorial: Set up an Azure-SSIS IR.

In this tutorial, you will:

  • Create a data factory.
  • Create an Azure-SSIS Integration Runtime.
  • Start the Azure-SSIS Integration Runtime.
  • Review the complete script.
  • Deploy SSIS packages.

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.

  • An Azure subscription. If you don’t have an Azure subscription, create a free account before you begin. For conceptual information about Azure-SSIS IR, see Azure-SSIS Integration Runtime overview.
  • (Optional) Azure SQL Database server. If you don’t already have a database server, create one in the Azure portal before you get started. Azure Data Factory will in turn create SSISDB 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 to SSISDB without crossing Azure regions.
    • Based on the selected database server, SSISDB can be created on your behalf as a single database, part of an elastic pool, or in a managed instance, and accessible in a public network or by joining a virtual network. For guidance in choosing the type of database server to host SSISDB, see Compare an Azure SQL Database single database, elastic pool, and managed instance.If you use an Azure SQL Database server with an IP firewall or 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 a self-hosted IR, join your Azure-SSIS IR to a virtual network. For more information, see Create an Azure-SSIS IR in a virtual network.
    • Confirm that the Allow access to Azure services setting is enabled for the database server. This setting isn’t applicable when you use an Azure SQL Database server with IP firewall rules or 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 including 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 Azure Active Directory (Azure AD) authentication with the managed identity for your data factory. For Azure AD authentication, to add the managed identity for your data factory to an Azure AD group with access permissions to the database server, see Create an Azure-SSIS IR with Azure AD authentication.
    • Confirm that your database server doesn’t already have an SSISDB. Setting up an Azure-SSIS IR doesn’t support using an existing SSISDB.
  • Azure PowerShell. To run a PowerShell script to set up your Azure-SSIS IR, follow the instructions in Install and configure Azure PowerShell.

 Note

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

Open the Windows PowerShell ISE

Open the Windows PowerShell Integrated Scripting Environment (ISE) with administrator permissions.

Create variables

Copy the following script to the ISE. Specify values for the variables.PowerShellCopy

### Azure Data Factory info
# If your input contains a PSH special character (for example, "$"), 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 info; 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, although Enterprise lets you use advanced/premium features on your Azure-SSIS IR
# Azure-SSIS IR hybrid usage info: LicenseIncluded or BasePrice
$AzureSSISLicenseType = "LicenseIncluded" # LicenseIncluded by default, while BasePrice lets you bring your own on-premises SQL Server license with Software Assurance to earn cost savings from Azure Hybrid Benefit (AHB) option
# For a Standard_D1_v2 node, up to 4 parallel executions per node are supported, but for other nodes, up to (2 x number of cores) are currently supported
$AzureSSISMaxParallelExecutionsPerNode = 8
# Custom setup info: Standard/express custom setups
$SetupScriptContainerSasUri = "" # OPTIONAL to provide a SAS URI of blob container for standard custom setup where your script and its associated files are stored
$ExpressCustomSetup = "[RunCmdkey|SetEnvironmentVariable|SentryOne.TaskFactory|oh22is.SQLPhonetics.NET|oh22is.HEDDA.IO|KingswaySoft.IntegrationToolkit|KingswaySoft.ProductivityPack|Theobald.XtractIS or leave it empty]" # OPTIONAL to configure an express custom setup without script

### SSISDB info
$SSISDBServerEndpoint = "[your Azure SQL Database server name.database.windows.net or managed instance name.public.DNS prefix.database.windows.net,3342 or leave it empty if you're not using SSISDB]" # WARNING: If you use SSISDB, please ensure that there is no existing SSISDB on your database server, so we can prepare and manage one on your behalf    
$SSISDBServerAdminUserName = "[your server admin username for SQL authentication]"
$SSISDBServerAdminPassword = "[your server admin password for SQL authentication]"
# For the basic pricing tier, specify "Basic", not "B" - For standard/premium/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]"

### Self-hosted integration runtime info - This can be configured as a proxy for on-premises data access 
$DataProxyIntegrationRuntimeName = "" # OPTIONAL to configure a proxy for on-premises data access 
$DataProxyStagingLinkedServiceName = "" # OPTIONAL to configure a proxy for on-premises data access 
$DataProxyStagingPath = "" # OPTIONAL to configure a proxy for on-premises data access 

Sign in and select your subscription

To sign in and select your Azure subscription, add the following code to the script:PowerShellCopy

Connect-AzAccount
Select-AzSubscription -SubscriptionName $SubscriptionName

Validate the connection to your database server

To validate your Azure SQL Database server, add the following script:PowerShellCopy

# Validate only if you're using SSISDB
if(![string]::IsNullOrEmpty($SSISDBServerEndpoint))
{
    $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;
        } 
    }
}

To create an Azure SQL Database instance as part of the script, see the following example. Set values for the variables that haven’t been defined already (for example, SSISDBServerName, FirewallIPAddress).PowerShellCopy

New-AzSqlServer -ResourceGroupName $ResourceGroupName `
    -ServerName $SSISDBServerName `
    -Location $DataFactoryLocation `
    -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $SSISDBServerAdminUserName, $(ConvertTo-SecureString -String $SSISDBServerAdminPassword -AsPlainText -Force))    

New-AzSqlServerFirewallRule -ResourceGroupName $ResourceGroupName `
    -ServerName $SSISDBServerName `
    -FirewallRuleName "ClientIPAddress_$today" -StartIpAddress $FirewallIPAddress -EndIpAddress $FirewallIPAddress

New-AzSqlServerFirewallRule -ResourceGroupName $ResourceGroupName -ServerName $SSISDBServerName -AllowAllAzureIPs

Create a resource group

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

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

New-AzResourceGroup -Location $DataFactoryLocation -Name $ResourceGroupName

Create a data factory

Run the following command to create a data factory:PowerShellCopy

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

Create an Azure-SSIS Integration Runtime

To create an Azure-SSIS Integration Runtime that runs SSIS packages in Azure, run the following commands. If you’re not using SSISDB, you can omit the CatalogServerEndpoint, CatalogPricingTier, and CatalogAdminCredential parameters.PowerShellCopy

Set-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
    -DataFactoryName $DataFactoryName `
    -Name $AzureSSISName `
    -Description $AzureSSISDescription `
    -Type Managed `
    -Location $AzureSSISLocation `
    -NodeSize $AzureSSISNodeSize `
    -NodeCount $AzureSSISNodeNumber `
    -Edition $AzureSSISEdition `
    -LicenseType $AzureSSISLicenseType `
    -MaxParallelExecutionsPerNode $AzureSSISMaxParallelExecutionsPerNode

# Add CatalogServerEndpoint, CatalogPricingTier, and CatalogAdminCredential parameters if you're using SSISDB
if(![string]::IsNullOrEmpty($SSISDBServerEndpoint))
{
    $secpasswd = ConvertTo-SecureString $SSISDBServerAdminPassword -AsPlainText -Force
    $serverCreds = New-Object System.Management.Automation.PSCredential($SSISDBServerAdminUserName, $secpasswd)

    Set-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
        -DataFactoryName $DataFactoryName `
        -Name $AzureSSISName `
        -CatalogServerEndpoint $SSISDBServerEndpoint `
        -CatalogPricingTier $SSISDBPricingTier `
        -CatalogAdminCredential $serverCreds
}

# Add custom setup parameters if you use standard/express custom setups
if(![string]::IsNullOrEmpty($SetupScriptContainerSasUri))
{
    Set-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
        -DataFactoryName $DataFactoryName `
        -Name $AzureSSISName `
        -SetupScriptContainerSasUri $SetupScriptContainerSasUri
}
if(![string]::IsNullOrEmpty($ExpressCustomSetup))
{
    if($ExpressCustomSetup -eq "RunCmdkey")
    {
        $addCmdkeyArgument = "YourFileShareServerName or YourAzureStorageAccountName.file.core.windows.net"
        $userCmdkeyArgument = "YourDomainName\YourUsername or azure\YourAzureStorageAccountName"
        $passCmdkeyArgument = New-Object Microsoft.Azure.Management.DataFactory.Models.SecureString("YourPassword or YourAccessKey")
        $setup = New-Object Microsoft.Azure.Management.DataFactory.Models.CmdkeySetup($addCmdkeyArgument, $userCmdkeyArgument, $passCmdkeyArgument)
    }
    if($ExpressCustomSetup -eq "SetEnvironmentVariable")
    {
        $variableName = "YourVariableName"
        $variableValue = "YourVariableValue"
        $setup = New-Object Microsoft.Azure.Management.DataFactory.Models.EnvironmentVariableSetup($variableName, $variableValue)
    }
    if($ExpressCustomSetup -eq "SentryOne.TaskFactory")
    {
        $licenseKey = New-Object Microsoft.Azure.Management.DataFactory.Models.SecureString("YourLicenseKey")
        $setup = New-Object Microsoft.Azure.Management.DataFactory.Models.ComponentSetup($ExpressCustomSetup, $licenseKey)
    }
    if($ExpressCustomSetup -eq "oh22is.SQLPhonetics.NET")
    {
        $licenseKey = New-Object Microsoft.Azure.Management.DataFactory.Models.SecureString("YourLicenseKey")
        $setup = New-Object Microsoft.Azure.Management.DataFactory.Models.ComponentSetup($ExpressCustomSetup, $licenseKey)
    }
    if($ExpressCustomSetup -eq "oh22is.HEDDA.IO")
    {
        $setup = New-Object Microsoft.Azure.Management.DataFactory.Models.ComponentSetup($ExpressCustomSetup)
    }
    if($ExpressCustomSetup -eq "KingswaySoft.IntegrationToolkit")
    {
        $licenseKey = New-Object Microsoft.Azure.Management.DataFactory.Models.SecureString("YourLicenseKey")
        $setup = New-Object Microsoft.Azure.Management.DataFactory.Models.ComponentSetup($ExpressCustomSetup, $licenseKey)
    }
    if($ExpressCustomSetup -eq "KingswaySoft.ProductivityPack")
    {
        $licenseKey = New-Object Microsoft.Azure.Management.DataFactory.Models.SecureString("YourLicenseKey")
        $setup = New-Object Microsoft.Azure.Management.DataFactory.Models.ComponentSetup($ExpressCustomSetup, $licenseKey)
    }    
    if($ExpressCustomSetup -eq "Theobald.XtractIS")
    {
        $jsonData = Get-Content -Raw -Path YourLicenseFile.json
        $jsonData = $jsonData -replace '\s',''
        $jsonData = $jsonData.replace('"','\"')
        $licenseKey = New-Object Microsoft.Azure.Management.DataFactory.Models.SecureString($jsonData)
        $setup = New-Object Microsoft.Azure.Management.DataFactory.Models.ComponentSetup($ExpressCustomSetup, $licenseKey)
    }
    # Create an array of one or more express custom setups
    $setups = New-Object System.Collections.ArrayList
    $setups.Add($setup)

    Set-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
        -DataFactoryName $DataFactoryName `
        -Name $AzureSSISName `
        -ExpressCustomSetup $setups
}

# Add self-hosted integration runtime parameters if you configure a proxy for on-premises data access
if(![string]::IsNullOrEmpty($DataProxyIntegrationRuntimeName) -and ![string]::IsNullOrEmpty($DataProxyStagingLinkedServiceName))
{
    Set-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
        -DataFactoryName $DataFactoryName `
        -Name $AzureSSISName `
        -DataProxyIntegrationRuntimeName $DataProxyIntegrationRuntimeName `
        -DataProxyStagingLinkedServiceName $DataProxyStagingLinkedServiceName

    if(![string]::IsNullOrEmpty($DataProxyStagingPath))
    {
        Set-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
            -DataFactoryName $DataFactoryName `
            -Name $AzureSSISName `
            -DataProxyStagingPath $DataProxyStagingPath
    }
}

Start the Azure-SSIS Integration Runtime

To start the Azure-SSIS IR, run the following commands:PowerShellCopy

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 be completed within five minutes.

If you’re using SSISDB, the Azure Data Factory service will connect to your database server to prepare SSISDB.

When you set up an Azure-SSIS IR, Access Redistributable and Azure Feature Pack for SSIS are also installed. These components provide connectivity to Excel/Access files and various Azure data sources, in addition to the data sources already supported by built-in components. You can also install additional components, see Custom setup for Azure-SSIS IR.

Full script

The PowerShell script in this section configures an instance of Azure-SSIS IR that runs SSIS packages. After you run this script successfully, you can deploy and run SSIS packages in Azure.

  1. Open the ISE.
  2. At the ISE command prompt, run the following command:PowerShellCopySet-ExecutionPolicy Unrestricted -Scope CurrentUser
  3. Copy the PowerShell script in this section to the ISE.
  4. Provide appropriate values for all parameters at the beginning of the script.
  5. Run the script.

PowerShellCopy

### Azure Data Factory info
# If your input contains a PSH special character, e.g. "$", precede it with the escape character "`" like "`$"
$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 info - 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, while Enterprise lets you use advanced/premium features on your Azure-SSIS IR
# Azure-SSIS IR hybrid usage info: LicenseIncluded or BasePrice
$AzureSSISLicenseType = "LicenseIncluded" # LicenseIncluded by default, while BasePrice lets you bring your own on-premises SQL Server license with Software Assurance to earn cost savings from Azure Hybrid Benefit (AHB) option
# For a Standard_D1_v2 node, up to 4 parallel executions per node are supported, but for other nodes, up to (2 x the number of cores) are currently supported
$AzureSSISMaxParallelExecutionsPerNode = 8
# Custom setup info: Standard/express custom setups
$SetupScriptContainerSasUri = "" # OPTIONAL to provide a SAS URI of blob container for standard custom setup where your script and its associated files are stored
$ExpressCustomSetup = "[RunCmdkey|SetEnvironmentVariable|SentryOne.TaskFactory|oh22is.SQLPhonetics.NET|oh22is.HEDDA.IO|KingswaySoft.IntegrationToolkit|KingswaySoft.ProductivityPack|Theobald.XtractIS or leave it empty]" # OPTIONAL to configure an express custom setup without script

### SSISDB info
$SSISDBServerEndpoint = "[your Azure SQL Database server name.database.windows.net or managed instance name.public.DNS prefix.database.windows.net,3342 or leave it empty if you're not using SSISDB]" # WARNING: If you want to use SSISDB, ensure that there is no existing SSISDB on your database server, so we can prepare and manage one on your behalf    
$SSISDBServerAdminUserName = "[your server admin username for SQL authentication]"
$SSISDBServerAdminPassword = "[your server admin password for SQL authentication]"
# For the basic pricing tier, specify "Basic", not "B" - For standard/premium/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]"

### Self-hosted integration runtime info - This can be configured as a proxy for on-premises data access 
$DataProxyIntegrationRuntimeName = "" # OPTIONAL to configure a proxy for on-premises data access 
$DataProxyStagingLinkedServiceName = "" # OPTIONAL to configure a proxy for on-premises data access 
$DataProxyStagingPath = "" # OPTIONAL to configure a proxy for on-premises data access 

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

### Validate the connection to database server
# Validate only if you're using SSISDB
if(![string]::IsNullOrEmpty($SSISDBServerEndpoint))
{
    $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;
        } 
    }
}

### 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

# Add CatalogServerEndpoint, CatalogPricingTier, and CatalogAdminCredential parameters if you're using SSISDB
if(![string]::IsNullOrEmpty($SSISDBServerEndpoint))
{
    $secpasswd = ConvertTo-SecureString $SSISDBServerAdminPassword -AsPlainText -Force
    $serverCreds = New-Object System.Management.Automation.PSCredential($SSISDBServerAdminUserName, $secpasswd)

    Set-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
        -DataFactoryName $DataFactoryName `
        -Name $AzureSSISName `
        -CatalogServerEndpoint $SSISDBServerEndpoint `
        -CatalogPricingTier $SSISDBPricingTier `
        -CatalogAdminCredential $serverCreds
}

# Add custom setup parameters if you use standard/express custom setups
if(![string]::IsNullOrEmpty($SetupScriptContainerSasUri))
{
    Set-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
        -DataFactoryName $DataFactoryName `
        -Name $AzureSSISName `
        -SetupScriptContainerSasUri $SetupScriptContainerSasUri
}
if(![string]::IsNullOrEmpty($ExpressCustomSetup))
{
    if($ExpressCustomSetup -eq "RunCmdkey")
    {
        $addCmdkeyArgument = "YourFileShareServerName or YourAzureStorageAccountName.file.core.windows.net"
        $userCmdkeyArgument = "YourDomainName\YourUsername or azure\YourAzureStorageAccountName"
        $passCmdkeyArgument = New-Object Microsoft.Azure.Management.DataFactory.Models.SecureString("YourPassword or YourAccessKey")
        $setup = New-Object Microsoft.Azure.Management.DataFactory.Models.CmdkeySetup($addCmdkeyArgument, $userCmdkeyArgument, $passCmdkeyArgument)
    }
    if($ExpressCustomSetup -eq "SetEnvironmentVariable")
    {
        $variableName = "YourVariableName"
        $variableValue = "YourVariableValue"
        $setup = New-Object Microsoft.Azure.Management.DataFactory.Models.EnvironmentVariableSetup($variableName, $variableValue)
    }
    if($ExpressCustomSetup -eq "SentryOne.TaskFactory")
    {
        $licenseKey = New-Object Microsoft.Azure.Management.DataFactory.Models.SecureString("YourLicenseKey")
        $setup = New-Object Microsoft.Azure.Management.DataFactory.Models.ComponentSetup($ExpressCustomSetup, $licenseKey)
    }
    if($ExpressCustomSetup -eq "oh22is.SQLPhonetics.NET")
    {
        $licenseKey = New-Object Microsoft.Azure.Management.DataFactory.Models.SecureString("YourLicenseKey")
        $setup = New-Object Microsoft.Azure.Management.DataFactory.Models.ComponentSetup($ExpressCustomSetup, $licenseKey)
    }
    if($ExpressCustomSetup -eq "oh22is.HEDDA.IO")
    {
        $setup = New-Object Microsoft.Azure.Management.DataFactory.Models.ComponentSetup($ExpressCustomSetup)
    }
    if($ExpressCustomSetup -eq "KingswaySoft.IntegrationToolkit")
    {
        $licenseKey = New-Object Microsoft.Azure.Management.DataFactory.Models.SecureString("YourLicenseKey")
        $setup = New-Object Microsoft.Azure.Management.DataFactory.Models.ComponentSetup($ExpressCustomSetup, $licenseKey)
    }
    if($ExpressCustomSetup -eq "KingswaySoft.ProductivityPack")
    {
        $licenseKey = New-Object Microsoft.Azure.Management.DataFactory.Models.SecureString("YourLicenseKey")
        $setup = New-Object Microsoft.Azure.Management.DataFactory.Models.ComponentSetup($ExpressCustomSetup, $licenseKey)
    }    
    if($ExpressCustomSetup -eq "Theobald.XtractIS")
    {
        $jsonData = Get-Content -Raw -Path YourLicenseFile.json
        $jsonData = $jsonData -replace '\s',''
        $jsonData = $jsonData.replace('"','\"')
        $licenseKey = New-Object Microsoft.Azure.Management.DataFactory.Models.SecureString($jsonData)
        $setup = New-Object Microsoft.Azure.Management.DataFactory.Models.ComponentSetup($ExpressCustomSetup, $licenseKey)
    }
    # Create an array of one or more express custom setups
    $setups = New-Object System.Collections.ArrayList
    $setups.Add($setup)

    Set-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
        -DataFactoryName $DataFactoryName `
        -Name $AzureSSISName `
        -ExpressCustomSetup $setups
}

# Add self-hosted integration runtime parameters if you configure a proxy for on-premises data access
if(![string]::IsNullOrEmpty($DataProxyIntegrationRuntimeName) -and ![string]::IsNullOrEmpty($DataProxyStagingLinkedServiceName))
{
    Set-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
        -DataFactoryName $DataFactoryName `
        -Name $AzureSSISName `
        -DataProxyIntegrationRuntimeName $DataProxyIntegrationRuntimeName `
        -DataProxyStagingLinkedServiceName $DataProxyStagingLinkedServiceName

    if(![string]::IsNullOrEmpty($DataProxyStagingPath))
    {
        Set-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
            -DataFactoryName $DataFactoryName `
            -Name $AzureSSISName `
            -DataProxyStagingPath $DataProxyStagingPath
    }
}

### Start 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.")   

Monitor and manage your Azure-SSIS IR

For information about monitoring and managing the Azure-SSIS IR, see:

Deploy SSIS packages

If you’re using SSISDB, you can deploy your packages to it and run them on the Azure-SSIS IR by using SQL Server Data Tools (SSDT) or SQL Server Management Studio (SSMS) tools that connect to your database server via its server endpoint. For your Azure SQL Database server instance or a managed instance with a public endpoint, the server endpoint formats are .database.windows.net and .public..database.windows.net,3342, respectively.

If you’re not using SSISDB, you can deploy your packages to file systems, file shares, or an Azure Files share and run them on the Azure-SSIS IR by using dtinstall/dtutil/dtexec command-line utilities. For more information, see Deploy SSIS packages.

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

For more SSIS documentation, see:

Next steps

In this tutorial, you learned how to:

  • Create a data factory.
  • Create an Azure-SSIS Integration Runtime.
  • Start the Azure-SSIS Integration Runtime.
  • Review the complete script.
  • Deploy SSIS packages.

Customize the setup for an Azure-SSIS Integration Runtime

APPLIES TO: yesAzure Data Factory yesAzure Synapse Analytics (Preview)

The custom setup for an Azure-SQL Server Integration Services Integration Runtime (Azure-SSIS IR) provides an interface for adding your own steps during the setup or reconfiguration of your Azure-SSIS IR.

By using the custom setup, you can alter the default operating configuration or environment to, for example, start additional Windows services, persist access credentials for file shares, or use strong cryptography/more secure network protocol (TLS 1.2). Or you can install additional components, such as assemblies, drivers, or extensions, on each node of your Azure-SSIS IR.

You can do custom setups on your Azure-SSIS IR in either of two ways:

  • Express custom setup without a script: Run some common system configurations and Windows commands or install some popular or recommended additional components without using any scripts.
  • Standard custom setup with a script: Prepare a script and its associated files, and upload them all together to a blob container in your Azure storage account. You then provide a Shared Access Signature (SAS) Uniform Resource Identifier (URI) for your container when you set up or reconfigure your Azure-SSIS IR. Each node of your Azure-SSIS IR then downloads the script and its associated files from your container and runs your custom setup with elevated permissions. When your custom setup is finished, each node uploads the standard output of execution and other logs to your container.

You can install both free, unlicensed components and paid, licensed components with express and standard custom setups. If you’re an independent software vendor (ISV), see Develop paid or licensed components for an Azure-SSIS IR.

 Important

To benefit from future enhancements, we recommend using v3 or later series of nodes for your Azure-SSIS IR with custom setup.

Current limitations

The following limitations apply only to standard custom setups:

  • If you want to use gacutil.exe in your script to install assemblies in the global assembly cache (GAC), you need to provide gacutil.exe as part of your custom setup. Or you can use the copy that’s provided in our Public Preview container, discussed later in the “Instructions” section.
  • If you want to reference a subfolder in your script, msiexec.exe doesn’t support the .\ notation to reference the root folder. Use a command such as msiexec /i "MySubfolder\MyInstallerx64.msi" ... instead of msiexec /i ".\MySubfolder\MyInstallerx64.msi" ....
  • Administrative shares, or hidden network shares that are automatically created by Windows, are currently not supported on the Azure-SSIS IR.
  • The IBM iSeries Access ODBC driver is not supported on the Azure-SSIS IR. You might see installation errors during your custom setup. If you do, contact IBM support for assistance.

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.

To customize your Azure-SSIS IR, you need the following items:

Instructions

  1. If you want to set up or reconfigure your Azure-SSIS IR with PowerShell, download and install Azure PowerShell. For express custom setups, skip to step 4.
  2. Prepare your custom setup script and its associated files (for example, .bat, .cmd, .exe, .dll, .msi, or .ps1 files).
    • You must have a script file named main.cmd, which is the entry point of your custom setup.
    • To ensure that the script can be executed silently, we recommend that you test it on your local machine first.
    • If you want additional logs generated by other tools (for example, msiexec.exe) to be uploaded to your container, specify the predefined environment variable, CUSTOM_SETUP_SCRIPT_LOG_DIR, as the log folder in your scripts (for example, msiexec /i xxx.msi /quiet /lv %CUSTOM_SETUP_SCRIPT_LOG_DIR%\install.log).
  3. Download, install, and open Azure Storage Explorer. To do so:a. Under (Local and Attached), right-click Storage Accounts, and then select Connect to Azure storage.Connect to Azure Storageb. Select Use a storage account name and key, and then select Next.Use a storage account name and keyc. Enter your Azure storage account name and key, select Next, and then select Connect.Provide storage account name and keyd. Under your connected Azure storage account, right-click Blob Containers, select Create Blob Container, and name the new container.Create a blob containere. Select the new container, and upload your custom setup script and its associated files. Make sure that you upload main.cmd at the top level of your container, not in any folder. Also ensure that your container contains only the necessary custom setup files, so that downloading them to your Azure-SSIS IR later won’t take a long time. The maximum duration of a custom setup is currently set at 45 minutes before it times out. This includes the time to download all files from your container and install them on the Azure-SSIS IR. If setup requires more time, raise a support ticket.Upload files to the blob containerf. Right-click the container, and then select Get Shared Access Signature.Get the Shared Access Signature for the containerg. Create the SAS URI for your container with a sufficiently long expiration time and with read/write/list permission. You need the SAS URI to download and run your custom setup script and its associated files whenever any node of your Azure-SSIS IR is reimaged or restarted. You need write permission to upload setup execution logs. ImportantEnsure that the SAS URI doesn’t expire and the custom setup resources are always available during the whole lifecycle of your Azure-SSIS IR, from creation to deletion, especially if you regularly stop and start your Azure-SSIS IR during this period.Generate the Shared Access Signature for the containerh. Copy and save the SAS URI of your container.Copy and save the Shared Access Signature
  4. When you set up or reconfigure your Azure-SSIS IR with a data factory UI, you can add or remove custom setups by selecting the Customize your Azure-SSIS Integration Runtime with additional system configurations/component installations check box in the Advanced Settings section of the Integration runtime setup pane.If you want to add standard custom setups, enter the SAS URI of your container in the Custom setup container SAS URI box.If you want to add express custom setups, select New to open the Add express custom setup pane, and then select a type in the Express custom setup type drop-down list:
    • If you select the Run cmdkey command type, you can persist access credentials for your file shares or Azure Files shares on Azure-SSIS IR by entering your targeted computer name or domain name, account name or username, and account key or password in the /Add/User, and /Pass boxes. This is similar to running the Windows cmdkey command on your local machine.
    • If you select the Add environment variable type, you can add Windows environment variables to use in your packages that run on the Azure-SSIS IR by entering your environment variable name and value in the Variable name and Variable value boxes. This is similar to running the Windows set command on your local machine.
    • If you select the Install licensed component type, you can then select an integrated component from our ISV partners in the Component name drop-down list:
      • If you select the SentryOne’s Task Factory component, you can install the Task Factory suite of components from SentryOne on your Azure-SSIS IR by entering the product license key that you purchased from them in the License key box. The current integrated version is 2019.4.3.
      • If you select the oh22’s HEDDA.IO component, you can install the HEDDA.IO data quality/cleansing component from oh22 on your Azure-SSIS IR after purchasing their service. The current integrated version is 1.0.13.
      • If you select the oh22’s SQLPhonetics.NET component, you can install the SQLPhonetics.NET data quality/matching component from oh22 on your Azure-SSIS IR by entering the product license key that you purchased from them in the License key box. The current integrated version is 1.0.43.
      • If you select the KingswaySoft’s SSIS Integration Toolkit component, you can install the SSIS Integration Toolkit suite of connectors for CRM/ERP/marketing/collaboration apps, such as Microsoft Dynamics/SharePoint/Project Server, Oracle/Salesforce Marketing Cloud, etc. from KingswaySoft on your Azure-SSIS IR by entering the product license key that you purchased from them in the License key box. The current integrated version is 2019.2.
      • If you select the KingswaySoft’s SSIS Productivity Pack component, you can install the SSIS Productivity Pack suite of components from KingswaySoft on your Azure-SSIS IR by entering the product license key that you purchased from them in the License key box. The current integrated version is 10.0.
      • If you select the Theobald Software’s Xtract IS component, you can install the Xtract IS suite of connectors for SAP system (ERP, S/4HANA, BW) from Theobald Software on your Azure-SSIS IR by dragging & dropping/uploading the product license file that you purchased from them into the License file box. The current integrated version is 6.1.1.3.
    Your added express custom setups will appear in the Advanced Settings section. To remove them, select their check boxes, and then select Delete.Advanced settings with custom setups
  5. When you set up or reconfigure your Azure-SSIS IR with PowerShell, you can add or remove the custom setups by running the Set-AzDataFactoryV2IntegrationRuntime cmdlet before you start your Azure-SSIS IR.PowerShellCopy$ResourceGroupName = "[your Azure resource group name]" $DataFactoryName = "[your data factory name]" $AzureSSISName = "[your Azure-SSIS IR name]" # Custom setup info: Standard/express custom setups $SetupScriptContainerSasUri = "" # OPTIONAL to provide a SAS URI of blob container for standard custom setup where your script and its associated files are stored $ExpressCustomSetup = "[RunCmdkey|SetEnvironmentVariable|SentryOne.TaskFactory|oh22is.SQLPhonetics.NET|oh22is.HEDDA.IO|KingswaySoft.IntegrationToolkit|KingswaySoft.ProductivityPack|Theobald.XtractIS or leave it empty]" # OPTIONAL to configure an express custom setup without script # Add custom setup parameters if you use standard/express custom setups if(![string]::IsNullOrEmpty($SetupScriptContainerSasUri)) { Set-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName ` -DataFactoryName $DataFactoryName ` -Name $AzureSSISName ` -SetupScriptContainerSasUri $SetupScriptContainerSasUri } if(![string]::IsNullOrEmpty($ExpressCustomSetup)) { if($ExpressCustomSetup -eq "RunCmdkey") { $addCmdkeyArgument = "YourFileShareServerName or YourAzureStorageAccountName.file.core.windows.net" $userCmdkeyArgument = "YourDomainName\YourUsername or azure\YourAzureStorageAccountName" $passCmdkeyArgument = New-Object Microsoft.Azure.Management.DataFactory.Models.SecureString("YourPassword or YourAccessKey") $setup = New-Object Microsoft.Azure.Management.DataFactory.Models.CmdkeySetup($addCmdkeyArgument, $userCmdkeyArgument, $passCmdkeyArgument) } if($ExpressCustomSetup -eq "SetEnvironmentVariable") { $variableName = "YourVariableName" $variableValue = "YourVariableValue" $setup = New-Object Microsoft.Azure.Management.DataFactory.Models.EnvironmentVariableSetup($variableName, $variableValue) } if($ExpressCustomSetup -eq "SentryOne.TaskFactory") { $licenseKey = New-Object Microsoft.Azure.Management.DataFactory.Models.SecureString("YourLicenseKey") $setup = New-Object Microsoft.Azure.Management.DataFactory.Models.ComponentSetup($ExpressCustomSetup, $licenseKey) } if($ExpressCustomSetup -eq "oh22is.SQLPhonetics.NET") { $licenseKey = New-Object Microsoft.Azure.Management.DataFactory.Models.SecureString("YourLicenseKey") $setup = New-Object Microsoft.Azure.Management.DataFactory.Models.ComponentSetup($ExpressCustomSetup, $licenseKey) } if($ExpressCustomSetup -eq "oh22is.HEDDA.IO") { $setup = New-Object Microsoft.Azure.Management.DataFactory.Models.ComponentSetup($ExpressCustomSetup) } if($ExpressCustomSetup -eq "KingswaySoft.IntegrationToolkit") { $licenseKey = New-Object Microsoft.Azure.Management.DataFactory.Models.SecureString("YourLicenseKey") $setup = New-Object Microsoft.Azure.Management.DataFactory.Models.ComponentSetup($ExpressCustomSetup, $licenseKey) } if($ExpressCustomSetup -eq "KingswaySoft.ProductivityPack") { $licenseKey = New-Object Microsoft.Azure.Management.DataFactory.Models.SecureString("YourLicenseKey") $setup = New-Object Microsoft.Azure.Management.DataFactory.Models.ComponentSetup($ExpressCustomSetup, $licenseKey) } if($ExpressCustomSetup -eq "Theobald.XtractIS") { $jsonData = Get-Content -Raw -Path YourLicenseFile.json $jsonData = $jsonData -replace '\s','' $jsonData = $jsonData.replace('"','\"') $licenseKey = New-Object Microsoft.Azure.Management.DataFactory.Models.SecureString($jsonData) $setup = New-Object Microsoft.Azure.Management.DataFactory.Models.ComponentSetup($ExpressCustomSetup, $licenseKey) } # Create an array of one or more express custom setups $setups = New-Object System.Collections.ArrayList $setups.Add($setup) Set-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName ` -DataFactoryName $DataFactoryName ` -Name $AzureSSISName ` -ExpressCustomSetup $setups } Start-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName ` -DataFactoryName $DataFactoryName ` -Name $AzureSSISName ` -Force After your standard custom setup finishes and your Azure-SSIS IR starts, you can find the standard output of main.cmd and other execution logs in the main.cmd.log folder of your storage container.
  6. To view some samples of standard custom setups, connect to our Public Preview container by using Azure Storage Explorer.a. Under (Local and Attached), right-click Storage Accounts, select Connect to Azure storage, select Use a connection string or a shared access signature URI, and then select Next.Connect to Azure storage with the Shared Access Signatureb. Select Use a SAS URI and then, in the URI box, enter the following SAS URI:https://ssisazurefileshare.blob.core.windows.net/publicpreview?sp=rl&st=2020-03-25T04:00:00Z&se=2025-03-25T04:00:00Z&sv=2019-02-02&sr=c&sig=WAD3DATezJjhBCO3ezrQ7TUZ8syEUxZZtGIhhP6Pt4I%3DProvide the Shared Access Signature for the containerc. Select Next, and then select Connect.d. In the left pane, select the connected publicpreview container, and then double-click the CustomSetupScript folder. In this folder are the following items:
    • Sample folder, which contains a custom setup to install a basic task on each node of your Azure-SSIS IR. The task does nothing but sleep for a few seconds. The folder also contains a gacutil folder, whose entire contents (gacutil.exegacutil.exe.config, and 1033\gacutlrc.dll) can be copied as is to your container.
    • UserScenarios folder, which contains several custom setup samples from real user scenarios.Contents of the public preview container
    e. Double-click the UserScenarios folder to find the following items:
    • .NET FRAMEWORK 3.5 folder, which contains a custom setup script (main.cmd) to install an earlier version of the .NET Framework that might be required for custom components on each node of your Azure-SSIS IR.
    • BCP folder, which contains a custom setup script (main.cmd) to install SQL Server command-line utilities (MsSqlCmdLnUtils.msi), including the bulk copy program (bcp), on each node of your Azure-SSIS IR.
    • An EXCEL folder, which contains a custom setup script (main.cmd) to install C# assemblies and libraries that you can use in script tasks to dynamically read and write Excel files on each node of your Azure-SSIS IR.First, download ExcelDataReader.dll and DocumentFormat.OpenXml.dll, and then upload them all together with main.cmd to your container. Alternatively, if you want to use only the standard Excel Connection Manager, Excel source, and Excel destination, the required access redistributable is already preinstalled on your Azure-SSIS IR, so you don’t need any custom setup.
    • MYSQL ODBC folder, which contains a custom setup script (main.cmd) to install the MySQL ODBC drivers on each node of your Azure-SSIS IR. This setup lets you use the ODBC Connection Manager, source, and destination to connect to the MySQL server.First, download the latest 64-bit and 32-bit versions of the MySQL ODBC driver installers (for example, mysql-connector-odbc-8.0.13-winx64.msi and mysql-connector-odbc-8.0.13-win32.msi), and then upload them all together with main.cmd to your container.
    • An ORACLE ENTERPRISE folder, which contains a custom setup script (main.cmd) and silent installation config file (client.rsp) to install the Oracle connectors and OCI driver on each node of your Azure-SSIS IR Enterprise Edition. This setup lets you use the Oracle Connection Manager, source, and destination to connect to the Oracle server.First, download Microsoft Connectors v5.0 for Oracle (AttunitySSISOraAdaptersSetup.msi and AttunitySSISOraAdaptersSetup64.msi) from Microsoft Download Center and the latest Oracle client (for example, winx64_12102_client.zip) from Oracle, and then upload them all together with main.cmd and client.rsp to your container. If you use TNS to connect to Oracle, you also need to download tnsnames.ora, edit it, and upload it to your container, so it can be copied to the Oracle installation folder during setup.
    • An ORACLE STANDARD ADO.NET folder, which contains a custom setup script (main.cmd) to install the Oracle ODP.NET driver on each node of your Azure-SSIS IR. This setup lets you use the ADO.NET Connection Manager, source, and destination to connect to the Oracle server.First, download the latest Oracle ODP.NET driver (for example, ODP.NET_Managed_ODAC122cR1.zip), and then upload it together with main.cmd to your container.
    • An ORACLE STANDARD ODBC folder, which contains a custom setup script (main.cmd) to install the Oracle ODBC driver and configure the data source name (DSN) on each node of your Azure-SSIS IR. This setup lets you use the ODBC Connection Manager, source, and destination or Power Query Connection Manager and source with the ODBC data source type to connect to the Oracle server.First, download the latest Oracle Instant Client (Basic Package or Basic Lite Package) and ODBC Package, and then upload them all together with main.cmd to your container:
      • Download 64-bit packages (Basic Package: instantclient-basic-windows.x64-18.3.0.0.0dbru.zip; Basic Lite Package: instantclient-basiclite-windows.x64-18.3.0.0.0dbru.zip; ODBC Package: instantclient-odbc-windows.x64-18.3.0.0.0dbru.zip)
      • Download 32-bit packages (Basic Package: instantclient-basic-nt-18.3.0.0.0dbru.zip; Basic Lite Package: instantclient-basiclite-nt-18.3.0.0.0dbru.zip; ODBC Package: instantclient-odbc-nt-18.3.0.0.0dbru.zip)
    • An ORACLE STANDARD OLEDB folder, which contains a custom setup script (main.cmd) to install the Oracle OLEDB driver on each node of your Azure-SSIS IR. This setup lets you use the OLEDB Connection Manager, source, and destination to connect to the Oracle server.First, download the latest Oracle OLEDB driver (for example, ODAC122010Xcopy_x64.zip), and then upload it together with main.cmd to your container.
    • POSTGRESQL ODBC folder, which contains a custom setup script (main.cmd) to install the PostgreSQL ODBC drivers on each node of your Azure-SSIS IR. This setup lets you use the ODBC Connection Manager, source, and destination to connect to the PostgreSQL server.First, download the latest 64-bit and 32-bit versions of PostgreSQL ODBC driver installers (for example, psqlodbc_x64.msi and psqlodbc_x86.msi), and then upload them all together with main.cmd to your container.
    • SAP BW folder, which contains a custom setup script (main.cmd) to install the SAP .NET connector assembly (librfc32.dll) on each node of your Azure-SSIS IR Enterprise Edition. This setup lets you use the SAP Business Warehouse (BW) Connection Manager, source, and destination to connect to the SAP BW server.First, upload the 64-bit or the 32-bit version of librfc32.dll from the SAP installation folder together with main.cmd to your container. The script then copies the SAP assembly to the %windir%\SysWow64 or %windir%\System32 folder during setup.
    • STORAGE folder, which contains a custom setup script (main.cmd) to install Azure PowerShell on each node of your Azure-SSIS IR. This setup lets you deploy and run SSIS packages that run PowerShell scripts to manipulate your Azure storage account.Copy main.cmd, a sample AzurePowerShell.msi (or use the latest version), and storage.ps1 to your container. Use PowerShell.dtsx as a template for your packages. The package template combines an Azure Blob download task, which downloads storage.ps1 as a modifiable PowerShell script, and an Execute process task, which executes the script on each node.
    • TERADATA folder, which contains a custom setup script (main.cmd), its associated file (install.cmd), and installer packages (.msi). These files install the Teradata connectors, the Teradata Parallel Transporter (TPT) API, and the ODBC driver on each node of your Azure-SSIS IR Enterprise Edition. This setup lets you use the Teradata Connection Manager, source, and destination to connect to the Teradata server.First, download the Teradata Tools and Utilities 15.x zip file (for example, TeradataToolsAndUtilitiesBase__windows_indep.15.10.22.00.zip), and then upload it together with the previously mentioned .cmd and .msi files to your container.
    • TLS 1.2 folder, which contains a custom setup script (main.cmd) to use strong cryptography/more secure network protocol (TLS 1.2) and disable older SSL/TLS versions on each node of your Azure-SSIS IR.
    • ZULU OPENJDK folder, which contains a custom setup script (main.cmd) and PowerShell file (install_openjdk.ps1) to install the Zulu OpenJDK on each node of your Azure-SSIS IR. This setup lets you use Azure Data Lake Store and Flexible File connectors to process ORC and Parquet files. For more information, see Azure Feature Pack for Integration Services.First, download the latest Zulu OpenJDK (for example, zulu8.33.0.1-jdk8.0.192-win_x64.zip), and then upload it together with main.cmd and install_openjdk.ps1 to your container.Folders in the user scenarios folder
    f. To try these custom setup samples, copy the content from the selected folder to your container.When you set up or reconfigure your Azure-SSIS IR by using the Data Factory UI, select the Customize your Azure-SSIS Integration Runtime with additional system configurations/component installations check box on the Advanced Settings section, and then enter the SAS URI of your container in the Custom setup container SAS URI box.When you set up or reconfigure your Azure-SSIS IR with PowerShell, run the Set-AzDataFactoryV2IntegrationRuntime cmdlet with the SAS URI of your container as the value for SetupScriptContainerSasUri parameter.

Add to Del.cio.us RSS Feed Add to Technorati Favorites Stumble It! Digg It!
    BuziTweet


Gerry Reid

Gerry Reid

“Technology leader with 20+ years experience in Agile IT Development, consulting, operations, delivery, project management in CRM, robotics, automation, cloud in financial services, telecoms & consulting sector",
https://crmanalytics.net/wp-content/uploads/2020/04/microsoft-data-integration-pipelines.jpghttps://crmanalytics.net/wp-content/uploads/2020/04/microsoft-data-integration-pipelines-150x150.jpgGerry ReidIntegration ToolsData Factory,SSISProvision Enterprise Edition for the Azure-SSIS Integration Runtime APPLIES TO: Azure Data Factory Azure Synapse Analytics (Preview) The Enterprise Edition of the Azure-SSIS Integration Runtime lets you use the following advanced and premium features: Change Data Capture (CDC) componentsOracle, Teradata, and SAP BW connectorsSQL Server Analysis Services (SSAS) and Azure Analysis Services (AAS) connectors...CRM consulting and technology for Ireland and Europe, in the Public and private sector