Published
Mar 16 2022 10:21 PM
4,235
Views
The Azure-SSIS Integration Runtime is a fully managed cluster of Azure VMs dedicated to run your SSIS packages. You can bring your own Azure SQL Database or SQL Managed Instance for the catalog of SSIS projects/packages (SSISDB).
To lift and shift existing SSIS workload, you can create an Azure-SSIS IR to natively execute SSIS packages.
An Azure-SSIS IR supports:
- Running packages deployed into SSIS catalog (SSISDB) hosted by Azure SQL Database server/Managed Instance (Project Deployment Model)
- Running packages deployed into file system, Azure Files, or SQL Server database (MSDB) hosted by Azure SQL Managed Instance (Package Deployment Model)
After an Azure-SSIS IR is provisioned, you can use familiar tools to deploy and run your packages in Azure.
It is recommended 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.
- 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.
- 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.
- 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.
- 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.
- You can connect to the database server by using SQL authentication with your server admin credentials, or by using Azure AD authentication with the specified system/user-assigned managed identity
- 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.
Prerequisites:
- Azure subscription.
- Azure SQL Database server or managed instance (optional).
- Az.Synapse Module
Create Azure SSIS IR in Azure Synapse using "Az.Synapse" module -
Create the variables:
Required Information
# Azure-SSIS Integration Runtime info;
$AzureSSISName = "[your Azure-SSIS IR name]"
$AzureSSISDescription = "[your Azure-SSIS IR description]"
# For supported regions, see https://azure.microsoft.com/en-us/global-infrastructure/services/?products=synapse-analytics®ions=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 existing 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
# SSISDB info
$SSISDBServerEndpoint = "[your server name.database.windows.net or managed instance name.public.DNS prefix.database.windows.net,3342 or leave 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
$SSISDBServerAdminCredentials = “PS Credentials”
# 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 SQL Database or leave it empty for SQL Managed Instance]"
Optional Information
# 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|InstallAzurePowerShell|SentryOne.TaskFactory|oh22is.SQLPhonetics.NET|oh22is.HEDDA.IO|KingswaySoft.IntegrationToolkit|KingswaySoft.ProductivityPack|Theobald.XtractIS|AecorSoft.IntegrationService|CData.Standard|CData.Extended or leave it empty]" # OPTIONAL to configure an express custom setup without script
### 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
Import “Az.Synapse” module:
Import-Module Az.Synapse #If module is not installed, try “Install-Module Az.Synapse”
Connect to Azure account:
Connect-AzAccount
Create Azure SSIS integration runtime in Synapse Workspace:
Please follow the syntax on "Set-AzSynapseIntegrationRuntime"
Set-AzSynapseIntegrationRuntime
-WorkspaceName
-ResourceGroupName
-Type Managed
-Name
-Description
-Location
-NodeSize
-NodeCount
-Edition
-LicenseType
-MaxParallelExecutionsPerNode
-CatalogServerEndpoint
-CatalogAdminCredential
-CatalogPricingTier
Example: Below example is to import module "Az.Synapse" & create a Azure SSIS IR in Synapse workspace.
PS C:\Users\MyUser> Import-Module Az.Synapse
WARNING: The names of some imported commands from the module 'Az.Synapse' include unapproved verbs that might make them less discoverable. To find the commands with unapproved verbs, run the Import-Module command again with the Verbose parameter. For a list of approved
verbs, type Get-Verb.
PS C:\Users\MyUser> Connect-AzAccount
WARNING: TenantId '********-****-****-****-**********' contains more than one active subscription. First one will be selected for further use. To select another subscription, use Set-AzContext.
WARNING: Unable to acquire token for tenant ‘********-****-****-****-**********' with error 'You must use multi-factor authentication to access tenant ********-****-****-****-**********, please rerun 'Connect-AzAccount' with additional parameter '-TenantId ********-****-****-****-**********'.'
Account SubscriptionName TenantId Environment
------- ---------------- -------- -----------
user@microsoft.com My Subscription ********-****-****-****-********** AzureCloud
PS C:\Users\MyUser> Get-AzSynapseWorkspace -WorkspaceName "[WorkspaceName]" -ResourceGroupName "[ResourceGroupName]"
DefaultDataLakeStorage : Microsoft.Azure.Commands.Synapse.Models.PSDataLakeStorageAccountDetails
ManagedResourceGroupName : [ResourceGroupName]
ProvisioningState : Succeeded
SqlAdministratorLogin : [SQLAdminLogin]
VirtualNetworkProfile :
Identity : Microsoft.Azure.Commands.Synapse.Models.PSManagedIdentity
ConnectivityEndpoints : {[web, https://web.azuresynapse.net?workspace=%2fsubscriptions%********-****-****-****-**********%2fresourceGroups%2f[ResourceGroupName]%2fproviders%2fMicrosoft.Synapse%2fworkspaces%2f[ResourceGroupName], [dev,https://[WorkspaceName].dev.azuresynapse.net], [sqlOnDemand, [WorkspaceName]-ondemand.sql.azuresynapse.net], [sql, [WorkspaceName].sql.azuresynapse.net]}
ManagedVirtualNetwork :
PrivateEndpointConnections : {}
WorkspaceUID : ********-****-****-****-**********
ExtraProperties : {[WorkspaceType, Normal], [IsScopeEnabled, False]}
ManagedVirtualNetworkSettings :
Encryption : Microsoft.Azure.Commands.Synapse.Models.PSEncryptionDetails
WorkspaceRepositoryConfiguration :
Tags : {}
TagsTable :
Location : [Region]
Id : /subscriptions/********-****-****-****-**********/resourceGroups/[ResourceGroupName]/providers/Microsoft.Synapse/workspaces/[WorkspaceName]
Name : [WorkspaceName]
Type : Microsoft.Synapse/workspaces
PS C:\Users\MyUser> $AzureSSISName = "MySSISIRinSynapse"
PS C:\Users\MyUser> $AzureSSISDescription = "Azure SSIS IR created using PowerShell in Azure Synapse Workspace"
PS C:\Users\MyUser> $AzureSSISLocation = "EastUS"
PS C:\Users\MyUser> $AzureSSISNodeSize = "Standard_D8_v3"
PS C:\Users\MyUser> $AzureSSISNodeNumber = 2
PS C:\Users\MyUser> $AzureSSISEdition = "Standard"
PS C:\Users\MyUser> $AzureSSISLicenseType = "LicenseIncluded"
PS C:\Users\MyUser> $AzureSSISMaxParallelExecutionsPerNode = 8
PS C:\Users\MyUser> $SSISDBServerEndpoint = "[SQLServer].database.windows.net"
PS C:\Users\MyUser> $SSISDBServerAdminCredentials = (Get-Credential)
cmdlet Get-Credential at command pipeline position 1
Supply values for the following parameters:
Credential
PS C:\Users\MyUser> $SSISDBPricingTier = "Basic"
PS C:\Users\MyUser> Set-AzSynapseIntegrationRuntime -WorkspaceName "ccalderasynapseworkspace" -ResourceGroupName "ccalderasynapseworkspacerg" -Type Managed -Name $AzureSSISName -Description $AzureSSISDescription -Location $AzureSSISLocation -NodeSize $AzureSSISNodeSize -NodeCount $AzureSSISNodeNumber -Edition $AzureSSISEdition -LicenseType $AzureSSISLicenseType -MaxParallelExecutionsPerNode $AzureSSISMaxParallelExecutionsPerNode -CatalogServerEndpoint $SSISDBServerEndpoint -CatalogAdminCredential $SSISDBServerAdminCredentials -CatalogPricingTier $SSISDBPricingTier
Location : EastUS
NodeSize : Standard_D8_v3
NodeCount : 2
MaxParallelExecutionsPerNode : 8
CatalogServerEndpoint : [SQLServer].database.windows.net
CatalogAdminUserName : [SQLDBAdminUser]
CatalogAdminPassword : **********
CatalogPricingTier : Basic
VNetId :
Subnet :
PublicIPs :
DataFlowCoreCount :
DataFlowComputeType :
DataFlowTimeToLive :
State : Initial
LicenseType : LicenseIncluded
SetupScriptContainerSasUri :
DataProxyIntegrationRuntimeName :
DataProxyStagingLinkedServiceName :
DataProxyStagingPath :
Edition : Standard
ExpressCustomSetup :
Name : MySSISIRinSynapse
Type : Managed
ResourceGroupName : [ResourceGroupName]
WorkspaceName : [WorkspaceName]
Description : Azure SSIS IR created using PowerShell in Azure Synapse Workspace
Id : /subscriptions/********-****-****-****-**********/resourceGroups/[ResourceGroupName]/providers/Microsoft.Synapse/workspaces/[WorkspaceName]/integrationruntimes/MySSISIRinSynapse
Once the Azure SSIS IR is successfully created, the new SSIS IR will be listed on "Integration runtimes" in Azure Synapse Analytics workspace -
Start the Azure SSIS IR Integration runtime:
Please follow the syntax on "Start-AzSynapseIntegrationRuntime"
Start-AzSynapseIntegrationRuntime
-WorkspaceName
-ResourceGroupName
-IntegrationRuntimeName
Example: Below example will show "How to start the Azure SSIS IR in Synapse workspace"
PS C:\Users\MyUser> Start-AzSynapseIntegrationRuntime -WorkspaceName "[WorkspaceName]" -ResourceGroupName "[ResourceGroupName]" -IntegrationRuntimeName "MySSISIRinSynapse"
Confirm
An integration runtime with the name MySSISIRinSynapse in the workspace ccalderasynapseworkspace exists.
Continuing execution may overwrite the existing one.
Are you sure you want to continue?
[Y] Yes [N] No [S] Suspend [?] Help (default is "Y"): Y
CreateTime : 2/22/2022 5:37:09 AM
Nodes : {}
OtherErrors : {}
LastOperation :
State : Started
Location : EastUS
NodeSize : Standard_D8_v3
NodeCount : 2
MaxParallelExecutionsPerNode : 8
CatalogServerEndpoint : [SQLServer].database.windows.net
CatalogAdminUserName : [SQLDBAdminUser]
CatalogAdminPassword : **********
CatalogPricingTier : Basic
VNetId :
Subnet :
PublicIPs :
DataFlowCoreCount :
DataFlowComputeType :
DataFlowTimeToLive :
LicenseType : LicenseIncluded
SetupScriptContainerSasUri :
DataProxyIntegrationRuntimeName :
DataProxyStagingLinkedServiceName :
DataProxyStagingPath :
Edition : Standard
ExpressCustomSetup :
Name : MySSISIRinSynapse
Type : Managed
ResourceGroupName : [ResourceGroupName]
WorkspaceName : [WorkspaceName]
Description : Azure SSIS IR created using PowerShell in Azure Synapse Workspace
Id : /subscriptions/********-****-****-****-**********/resourceGroups/[ResourceGroupName]/providers/Microsoft.Synapse/workspaces/[WorkspaceName]/integrationruntimes/MySSISIRinSynapse
If the deployment fails with a error, please refer "Troubleshoot SSIS Integration Runtime management" for more information.
Additional Information: