APPLIES TO: Azure Data Factory Azure Synapse Analytics
This article provides steps for provisioning an Azure-SQL Server Integration Services (SSIS) integration runtime (IR) in Azure Data Factory (ADF) and Azure Synapse Pipelines. An Azure-SSIS IR supports:
Note
There are certain features that aren't available for Azure-SSIS IR in Azure Synapse Analytics, check the limitations.
After an Azure-SSIS IR is provisioned, you can use familiar tools to deploy and run your packages in Azure. These tools are already Azure-enabled and include SQL Server Data Tools (SSDT), SQL Server Management Studio (SSMS), and command-line utilities like dtutil and AzureDTExec.
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 IP firewall rules/virtual network service endpoints or a managed instance with 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 Microsoft Entra authentication with the specified system/user-assigned 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 specified system/user-assigned 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 proxy for your Azure-SSIS IR to access data on-premises.
These articles show how to provision an Azure-SSIS IR by using the Azure portal, Azure PowerShell, and an Azure Resource Manager template.
PrerequisitesAzure subscription. If you don't already have a subscription, you can create a free trial account.
Azure SQL Database server or SQL Managed Instance (optional). If you don't already have a database server or managed instance, 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 or managed instance 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:
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 between SQL Database and SQL Managed Instance to host SSISDB, see the Compare SQL Database and SQL Managed Instance section in this article.
If you use an Azure SQL Database server with IP firewall rules/virtual network service endpoints or a SQL 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 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 isn't applicable when you use an Azure SQL Database server with IP firewall rules/virtual network service endpoints or a SQL managed instance with private endpoint to host SSISDB. For more information, see Secure 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 Microsoft Entra authentication with the specified system/user-assigned managed identity for your data factory. For the latter, you need to add the specified system/user-assigned managed identity for your data factory into a Microsoft Entra group with access permissions to the database server. For more information, see Enable Microsoft Entra authentication for an Azure-SSIS IR.
Confirm that your database server doesn't have an SSISDB instance already. The provisioning of an Azure-SSIS IR doesn't 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 IP firewall rules/virtual network service endpoints or a managed instance with 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.
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 SQL Database and SQL Managed InstanceThe following table compares certain features of an Azure SQL Database server and SQL Managed Instance as they relate to Azure-SSIR IR:
Feature SQL Database SQL Managed instance Scheduling The SQL Server Agent isn't 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 Microsoft Entra group with the managed identity of your data factory as a member in the db_owner role.See Enable Microsoft Entra authentication to create an SSISDB in 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 Microsoft Entra authentication to create an SSISDB in Azure SQL 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 an Azure Resource Manager virtual network if you use an Azure SQL Database server with IP firewall rules/virtual network service endpoints. Your Azure-SSIS IR can join an Azure Resource Manager virtual network if you use a managed instance with private endpoint. 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 aren't 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.RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4