通过 Azure PowerShell 创建 Azure-SSIS 集成运行时
适用于: Azure 数据工厂 Azure Synapse Analytics
提示
试用 Microsoft Fabric 中的数据工厂,这是一种适用于企业的一站式分析解决方案。 Microsoft Fabric 涵盖从数据移动到数据科学、实时分析、商业智能和报告的所有内容。 了解如何免费开始新的试用!
本文介绍如何通过 Azure PowerShell 在 Azure 数据工厂 (ADF) 中创建 Azure-SQL Server 集成服务 (SSIS) 集成运行时 (IR)。
注意
对于 Azure Synapse Analytics 中的 Azure-SSIS IR,替换为相应的 Azure Synapse Analytics PowerShell 接口:Set-AzSynapseIntegrationRuntime (Az.Synapse), Start-AzSynapseIntegrationRuntime 和 Stop-AzSynapseIntegrationRuntime。
创建变量
复制并粘贴以下脚本。 指定变量的值。
### Azure Data Factory info
# If your input contains a PSH special character like "$", precede it with the escape character "`" - for example, "`$"
$SubscriptionName = "[your Azure subscription name]"
$ResourceGroupName = "[your Azure resource group name]"
# Data factory name - must be globally unique
$DataFactoryName = "[your data factory name]"
# For supported regions, see https://azure.microsoft.com/global-infrastructure/services/?regions=china-non-regional,china-east,china-east-2,china-north,china-north-2&products=data-factory
$DataFactoryLocation = "ChinaEast2"
### 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/?regions=china-non-regional,china-east,china-east-2,china-north,china-north-2&products=data-factory
$AzureSSISLocation = "ChinaEast2"
# For supported node sizes, see https://azure.cn/pricing/details/data-factory/ssis/
$AzureSSISNodeSize = "Standard_D8_v3"
# 1-10 nodes are currently supported
$AzureSSISNodeNumber = 2
# Azure-SSIS IR edition/license info: Standard or Enterprise
$AzureSSISEdition = "Standard" # Standard by default, whereas Enterprise lets you use advanced features on your Azure-SSIS IR
# Azure-SSIS IR hybrid usage info: LicenseIncluded or BasePrice
$AzureSSISLicenseType = "LicenseIncluded" # LicenseIncluded by default, whereas BasePrice lets you bring your own on-premises SQL Server license with Software Assurance to earn cost savings from Azure Hybrid Benefit option
# For a Standard_D1_v2 node, up to 4 parallel executions per node are supported. For other nodes, up to (2 x number of cores) are currently supported.
$AzureSSISMaxParallelExecutionsPerNode = 8
# Custom setup info: 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
# Virtual network info: Azure Resource Manager or Classic
$VnetId = "[your virtual network resource ID or leave it empty]" # REQUIRED if you use Azure SQL Database server configured with a private endpoint/IP firewall rule/virtual network service endpoint or Azure SQL Managed Instance that joins a virtual network to host SSISDB, or if you require access to on-premises data without configuring a self-hosted IR. We recommend Azure Resource Manager virtual network, because classic virtual network will be deprecated soon.
$SubnetName = "[your subnet name or leave it empty]" # WARNING: Use the same subnet as the one used for Azure SQL Database server configured with a virtual network service endpoint or a different subnet from the one used for Azure SQL Managed Instance that joins a virtual network
$SubnetId = $VnetId + '/subnets/' + $SubnetName
# Virtual network injection method: Standard or Express. For comparison, see https://docs.azure.cn/data-factory/azure-ssis-integration-runtime-virtual-network-configuration.
$VnetInjectionMethod = "Standard" # Standard by default, whereas Express lets you use the express virtual network injection method
# Public IP address info: OPTIONAL to provide two standard static public IP addresses with DNS name under the same subscription and in the same region as your virtual network
$FirstPublicIP = "[your first public IP address resource ID or leave it empty]"
$SecondPublicIP = "[your second public IP address resource ID or leave it empty]"
### SSISDB info
$SSISDBServerEndpoint = "[your Azure SQL Database server name.database.chinacloudapi.cn or managed instance name.DNS prefix.database.chinacloudapi.cn or managed instance name.public.DNS prefix.database.chinacloudapi.cn,3342 or leave it empty if you do not use SSISDB]" # WARNING: If you use SSISDB, ensure that there's no existing SSISDB on your database server, so we can prepare and manage one on your behalf
# Authentication info: SQL or Azure AD
$SSISDBServerAdminUserName = "[your server admin username for SQL authentication or leave it empty for Azure AD authentication]"
$SSISDBServerAdminPassword = "[your server admin password for SQL authentication or leave it empty for Azure AD authentication]"
# For the basic pricing tier, specify "Basic," not "B." For standard, premium, and elastic pool tiers, specify "S0," "S1," "S2," "S3," etc. See https://docs.azure.cn/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
登录并选择订阅
添加以下脚本,以登录并选择 Azure 订阅。
Connect-AzAccount -Environment AzureChinaCloud
Select-AzSubscription -SubscriptionName $SubscriptionName
验证与数据库服务器的连接
添加以下脚本来验证 Azure SQL 数据库服务器或托管实例。
# Validate only if you use SSISDB and you don't use virtual network or Azure AD authentication
if(![string]::IsNullOrEmpty($SSISDBServerEndpoint))
{
if([string]::IsNullOrEmpty($VnetId) -and [string]::IsNullOrEmpty($SubnetName))
{
if(![string]::IsNullOrEmpty($SSISDBServerAdminUserName) -and ![string]::IsNullOrEmpty($SSISDBServerAdminPassword))
{
$SSISDBConnectionString = "Data Source=" + $SSISDBServerEndpoint + ";User ID=" + $SSISDBServerAdminUserName + ";Password=" + $SSISDBServerAdminPassword
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $SSISDBConnectionString;
Try
{
$sqlConnection.Open();
}
Catch [System.Data.SqlClient.SqlException]
{
Write-Warning "Cannot connect to your Azure SQL Database server, exception: $_";
Write-Warning "Please make sure the server you specified has already been created. Do you want to proceed? [Y/N]"
$yn = Read-Host
if(!($yn -ieq "Y"))
{
Return;
}
}
}
}
}
配置虚拟网络
添加以下脚本以自动配置加入 Azure-SSIS 集成运行时的虚拟网络权限和设置。
# Make sure to run this script against the subscription to which the virtual network belongs
if(![string]::IsNullOrEmpty($VnetId) -and ![string]::IsNullOrEmpty($SubnetName))
{
# Register to the Azure Batch resource provider
$BatchApplicationId = "ddbf3205-c6bd-46ae-8127-60eb93363864"
$BatchObjectId = (Get-AzADServicePrincipal -ServicePrincipalName $BatchApplicationId).Id
Register-AzResourceProvider -ProviderNamespace Microsoft.Batch
while(!(Get-AzResourceProvider -ProviderNamespace "Microsoft.Batch").RegistrationState.Contains("Registered"))
{
Start-Sleep -s 10
}
if($VnetId -match "/providers/Microsoft.ClassicNetwork/")
{
# Assign the VM contributor role to Microsoft.Batch
New-AzRoleAssignment -ObjectId $BatchObjectId -RoleDefinitionName "Classic Virtual Machine Contributor" -Scope $VnetId
}
}
创建资源组
使用 New-AzResourceGroup 命令创建 Azure 资源组。 资源组是在其中以组的形式部署和管理 Azure 资源的逻辑容器。
如果资源组已存在,请不要将此代码复制到脚本中。
New-AzResourceGroup -Location $DataFactoryLocation -Name $ResourceGroupName
创建数据工厂
运行以下命令创建数据工厂。
Set-AzDataFactoryV2 -ResourceGroupName $ResourceGroupName `
-Location $DataFactoryLocation `
-Name $DataFactoryName
创建集成运行时
运行以下命令,在 Azure 中创建运行 SSIS 包的 Azure-SSIS 集成运行时:
如果不使用 SSISDB,则可以省略 CatalogServerEndpoint
、CatalogPricingTier
和 CatalogAdminCredential
参数。
如果不使用具有 IP 防火墙规则/虚拟网络服务终结点的 Azure SQL 数据库服务器或具有专用终结点的托管实例来承载 SSISDB,或者如果需要访问本地数据,则可省略 VNetId
和 Subnet
参数,或传递这些参数的空值。 如果将自承载 IR 配置为 Azure-SSIS IR 的代理以访问本地数据,则也可以忽略这些参数。 否则不能省略这些参数,且必须传递虚拟网络配置中的有效值。 有关详细信息,请参阅将 Azure-SSIS IR 加入虚拟网络。
如果使用托管实例来承载 SSISDB,则可以省略 CatalogPricingTier
参数,或传递该参数的空值。 否则不能省略此参数,且必须传递 Azure SQL 数据库支持的定价层列表中的有效值。 有关详细信息,请参阅 SQL 数据库资源限制。
如果对数据工厂指定系统/用户分配的托管标识使用 Microsoft Entra 身份验证来连接到数据库服务器,可以省略 CatalogAdminCredential
参数。 但是,必须将为数据工厂指定的系统/用户分配的托管标识添加到有权访问数据库服务器的 Microsoft Entra 组中。 有关详细信息,请参阅为 Azure-SSIS IR 启用 Microsoft Entra 身份验证。 否则,不能忽略此参数,且必须传递由 SQL 身份验证的服务器管理员用户名和密码构成的有效对象。
Set-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
-DataFactoryName $DataFactoryName `
-Name $AzureSSISName `
-Description $AzureSSISDescription `
-Type Managed `
-Location $AzureSSISLocation `
-NodeSize $AzureSSISNodeSize `
-NodeCount $AzureSSISNodeNumber `
-Edition $AzureSSISEdition `
-LicenseType $AzureSSISLicenseType `
-MaxParallelExecutionsPerNode $AzureSSISMaxParallelExecutionsPerNode `
-SubnetId $SubnetId `
-VNetInjectionMethod $VnetInjectionMethod
# Add the CatalogServerEndpoint, CatalogPricingTier, and CatalogAdminCredential parameters if you use SSISDB
if(![string]::IsNullOrEmpty($SSISDBServerEndpoint))
{
Set-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
-DataFactoryName $DataFactoryName `
-Name $AzureSSISName `
-CatalogServerEndpoint $SSISDBServerEndpoint `
-CatalogPricingTier $SSISDBPricingTier
if(![string]::IsNullOrEmpty($SSISDBServerAdminUserName) -and ![string]::IsNullOrEmpty($SSISDBServerAdminPassword)) # Add the CatalogAdminCredential parameter if you don't use Azure AD authentication
{
$secpasswd = ConvertTo-SecureString $SSISDBServerAdminPassword -AsPlainText -Force
$serverCreds = New-Object System.Management.Automation.PSCredential($SSISDBServerAdminUserName, $secpasswd)
Set-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
-DataFactoryName $DataFactoryName `
-Name $AzureSSISName `
-CatalogAdminCredential $serverCreds
}
}
# Add 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.chinacloudapi.cn"
$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 "InstallAzurePowerShell")
{
$moduleVersion = "YourAzModuleVersion"
$setup = New-Object Microsoft.Azure.Management.DataFactory.Models.AzPowerShellSetup($moduleVersion)
}
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)
}
if($ExpressCustomSetup -eq "AecorSoft.IntegrationService")
{
$licenseKey = New-Object Microsoft.Azure.Management.DataFactory.Models.SecureString("YourLicenseKey")
$setup = New-Object Microsoft.Azure.Management.DataFactory.Models.ComponentSetup($ExpressCustomSetup, $licenseKey)
}
if($ExpressCustomSetup -eq "CData.Standard")
{
$licenseKey = New-Object Microsoft.Azure.Management.DataFactory.Models.SecureString("YourLicenseKey")
$setup = New-Object Microsoft.Azure.Management.DataFactory.Models.ComponentSetup($ExpressCustomSetup, $licenseKey)
}
if($ExpressCustomSetup -eq "CData.Extended")
{
$licenseKey = New-Object Microsoft.Azure.Management.DataFactory.Models.SecureString("YourLicenseKey")
$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
}
}
# Add public IP address parameters if you use the standard virtual network injection method and bring your own static public IP addresses
if($VnetInjectionMethod -eq "Standard")
{
if(![string]::IsNullOrEmpty($FirstPublicIP) -and ![string]::IsNullOrEmpty($SecondPublicIP))
{
$publicIPs = @($FirstPublicIP, $SecondPublicIP)
Set-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
-DataFactoryName $DataFactoryName `
-Name $AzureSSISName `
-PublicIPs $publicIPs
}
}
启动集成运行时
运行以下命令以启动 Azure-SSIS Integration Runtime。
write-host("##### Starting #####")
Start-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
-DataFactoryName $DataFactoryName `
-Name $AzureSSISName `
-Force
write-host("##### Completed #####")
write-host("If any cmdlet is unsuccessful, please consider using -Debug option for diagnostics.")
注意
此过程应在 5 分钟内完成(不包括任何自定义安装时间)。 但是,对于 standard 注入方法,Azure-SSIS IR 可能需要 20-30 分钟才能加入虚拟网络。
如果使用 SSISDB,数据工厂服务将连接到数据库服务器以准备 SSISDB。 它还会配置虚拟网络的权限和设置(如果已指定),并将 Azure-SSIS IR 加入虚拟网络中。
预配 Azure-SSIS IR 时,还会安装 Access Redistributable 和 Azure Feature Pack for SSIS。 除了内置组件已支持的数据源外,这些组件还提供与 Excel 文件、Access 文件和各种 Azure 数据源的连接。 有关内置/已预安装组件的详细信息,请参阅 Azure-SSIS IR 上的内置/已预安装组件。 有关可安装的其他组件的详细信息,请参阅 Azure-SSIS IR 的自定义安装。
完整脚本
下面是创建 Azure-SSIS 集成运行时的完整脚本。
### Azure Data Factory info
# If your input contains a PSH special character like "$", precede it with the escape character "`" - for example, "`$"
$SubscriptionName = "[your Azure subscription name]"
$ResourceGroupName = "[your Azure resource group name]"
# Data factory name - must be globally unique
$DataFactoryName = "[your data factory name]"
# For supported regions, see https://azure.microsoft.com/global-infrastructure/services/?regions=china-non-regional,china-east,china-east-2,china-north,china-north-2&products=data-factory
$DataFactoryLocation = "ChinaEast2"
### 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/?regions=china-non-regional,china-east,china-east-2,china-north,china-north-2&products=data-factory
$AzureSSISLocation = "ChinaEast2"
# For supported node sizes, see https://azure.cn/pricing/details/data-factory/ssis/
$AzureSSISNodeSize = "Standard_D8_v3"
# 1-10 nodes are currently supported
$AzureSSISNodeNumber = 2
# Azure-SSIS IR edition/license info: Standard or Enterprise
$AzureSSISEdition = "Standard" # Standard by default, whereas Enterprise lets you use advanced features on your Azure-SSIS IR
# Azure-SSIS IR hybrid usage info: LicenseIncluded or BasePrice
$AzureSSISLicenseType = "LicenseIncluded" # LicenseIncluded by default, whereas BasePrice lets you bring your own on-premises SQL Server license with Software Assurance to earn cost savings from the Azure Hybrid Benefit option
# For a Standard_D1_v2 node, up to four parallel executions per node are supported. For other nodes, up to (2 x number of cores) are currently supported.
$AzureSSISMaxParallelExecutionsPerNode = 8
# Custom setup info: 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
# Virtual network info: Azure Resource Manager or Classic
$VnetId = "[your virtual network resource ID or leave it empty]" # REQUIRED if you use Azure SQL Database server configured with a private endpoint/IP firewall rule/virtual network service endpoint or Azure SQL Managed Instance that joins a virtual network to host SSISDB, or if you require access to on-premises data without configuring a self-hosted IR. We recommend Azure Resource Manager virtual network, because classic virtual network will be deprecated soon.
$SubnetName = "[your subnet name or leave it empty]" # WARNING: Use the same subnet as the one used for Azure SQL Database server configured with a virtual network service endpoint or a different subnet from the one used for Azure SQL Managed Instance that joins a virtual network
$SubnetId = $VnetId + '/subnets/' + $SubnetName
# Virtual network injection method: Standard or Express. For comparison, see https://docs.azure.cn/data-factory/azure-ssis-integration-runtime-virtual-network-configuration.
$VnetInjectionMethod = "Standard" # Standard by default, whereas Express lets you use the express virtual network injection method
# Public IP address info: OPTIONAL to provide two standard static public IP addresses with DNS name under the same subscription and in the same region as your virtual network
$FirstPublicIP = "[your first public IP address resource ID or leave it empty]"
$SecondPublicIP = "[your second public IP address resource ID or leave it empty]"
### SSISDB info
$SSISDBServerEndpoint = "[your Azure SQL Database server name.database.chinacloudapi.cn or managed instance name.DNS prefix.database.chinacloudapi.cn or managed instance name.public.DNS prefix.database.chinacloudapi.cn,3342 or leave it empty if you do not use SSISDB]" # WARNING: If you use SSISDB, ensure that there's no existing SSISDB on your database server, so we can prepare and manage one on your behalf
# Authentication info: SQL or Azure AD
$SSISDBServerAdminUserName = "[your server admin username for SQL authentication or leave it empty for Azure AD authentication]"
$SSISDBServerAdminPassword = "[your server admin password for SQL authentication or leave it empty for Azure AD authentication]"
# For the basic pricing tier, specify "Basic," not "B." For standard, premium, and elastic pool tiers, specify "S0," "S1," "S2," "S3," etc. See https://docs.azure.cn/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 a subscription
Connect-AzAccount -Environment AzureChinaCloud
Select-AzSubscription -SubscriptionName $SubscriptionName
### Validate the connection to the database server
# Validate only if you use SSISDB and don't use a virtual network or Azure AD authentication
if(![string]::IsNullOrEmpty($SSISDBServerEndpoint))
{
if([string]::IsNullOrEmpty($VnetId) -and [string]::IsNullOrEmpty($SubnetName))
{
if(![string]::IsNullOrEmpty($SSISDBServerAdminUserName) -and ![string]::IsNullOrEmpty($SSISDBServerAdminPassword))
{
$SSISDBConnectionString = "Data Source=" + $SSISDBServerEndpoint + ";User ID=" + $SSISDBServerAdminUserName + ";Password=" + $SSISDBServerAdminPassword
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $SSISDBConnectionString;
Try
{
$sqlConnection.Open();
}
Catch [System.Data.SqlClient.SqlException]
{
Write-Warning "Cannot connect to your Azure SQL Database server, exception: $_";
Write-Warning "Please make sure the server you specified has already been created. Do you want to proceed? [Y/N]"
$yn = Read-Host
if(!($yn -ieq "Y"))
{
Return;
}
}
}
}
}
### Configure a virtual network
# Make sure to run this script against the subscription to which the virtual network belongs
if(![string]::IsNullOrEmpty($VnetId) -and ![string]::IsNullOrEmpty($SubnetName))
{
# Register to the Azure Batch resource provider
$BatchApplicationId = "ddbf3205-c6bd-46ae-8127-60eb93363864"
$BatchObjectId = (Get-AzADServicePrincipal -ServicePrincipalName $BatchApplicationId).Id
Register-AzResourceProvider -ProviderNamespace Microsoft.Batch
while(!(Get-AzResourceProvider -ProviderNamespace "Microsoft.Batch").RegistrationState.Contains("Registered"))
{
Start-Sleep -s 10
}
if($VnetId -match "/providers/Microsoft.ClassicNetwork/")
{
# Assign the VM contributor role to Microsoft.Batch
New-AzRoleAssignment -ObjectId $BatchObjectId -RoleDefinitionName "Classic Virtual Machine Contributor" -Scope $VnetId
}
}
### Create a data factory
Set-AzDataFactoryV2 -ResourceGroupName $ResourceGroupName `
-Location $DataFactoryLocation `
-Name $DataFactoryName
### Create an integration runtime
Set-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
-DataFactoryName $DataFactoryName `
-Name $AzureSSISName `
-Description $AzureSSISDescription `
-Type Managed `
-Location $AzureSSISLocation `
-NodeSize $AzureSSISNodeSize `
-NodeCount $AzureSSISNodeNumber `
-Edition $AzureSSISEdition `
-LicenseType $AzureSSISLicenseType `
-MaxParallelExecutionsPerNode $AzureSSISMaxParallelExecutionsPerNode `
-SubnetId $SubnetId `
-VNetInjectionMethod $VnetInjectionMethod
# Add CatalogServerEndpoint, CatalogPricingTier, and CatalogAdminCredential parameters if you use SSISDB
if(![string]::IsNullOrEmpty($SSISDBServerEndpoint))
{
Set-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
-DataFactoryName $DataFactoryName `
-Name $AzureSSISName `
-CatalogServerEndpoint $SSISDBServerEndpoint `
-CatalogPricingTier $SSISDBPricingTier
if(![string]::IsNullOrEmpty($SSISDBServerAdminUserName) -and ![string]::IsNullOrEmpty($SSISDBServerAdminPassword)) # Add the CatalogAdminCredential parameter if you don't use Azure AD authentication
{
$secpasswd = ConvertTo-SecureString $SSISDBServerAdminPassword -AsPlainText -Force
$serverCreds = New-Object System.Management.Automation.PSCredential($SSISDBServerAdminUserName, $secpasswd)
Set-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
-DataFactoryName $DataFactoryName `
-Name $AzureSSISName `
-CatalogAdminCredential $serverCreds
}
}
# Add 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.chinacloudapi.cn"
$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 "InstallAzurePowerShell")
{
$moduleVersion = "YourAzModuleVersion"
$setup = New-Object Microsoft.Azure.Management.DataFactory.Models.AzPowerShellSetup($moduleVersion)
}
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)
}
if($ExpressCustomSetup -eq "AecorSoft.IntegrationService")
{
$licenseKey = New-Object Microsoft.Azure.Management.DataFactory.Models.SecureString("YourLicenseKey")
$setup = New-Object Microsoft.Azure.Management.DataFactory.Models.ComponentSetup($ExpressCustomSetup, $licenseKey)
}
if($ExpressCustomSetup -eq "CData.Standard")
{
$licenseKey = New-Object Microsoft.Azure.Management.DataFactory.Models.SecureString("YourLicenseKey")
$setup = New-Object Microsoft.Azure.Management.DataFactory.Models.ComponentSetup($ExpressCustomSetup, $licenseKey)
}
if($ExpressCustomSetup -eq "CData.Extended")
{
$licenseKey = New-Object Microsoft.Azure.Management.DataFactory.Models.SecureString("YourLicenseKey")
$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
}
}
# Add public IP address parameters if you use the standard virtual network injection method and bring your own static public IP addresses
if($VnetInjectionMethod -eq "Standard")
{
if(![string]::IsNullOrEmpty($FirstPublicIP) -and ![string]::IsNullOrEmpty($SecondPublicIP))
{
$publicIPs = @($FirstPublicIP, $SecondPublicIP)
Set-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
-DataFactoryName $DataFactoryName `
-Name $AzureSSISName `
-PublicIPs $publicIPs
}
}
### Start the integration runtime
write-host("##### Starting #####")
Start-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
-DataFactoryName $DataFactoryName `
-Name $AzureSSISName `
-Force
write-host("##### Completed #####")
write-host("If any cmdlet is unsuccessful, please consider using -Debug option for diagnostics.")
相关内容
有关 Azure-SSIS IR 的详细信息,请参阅以下文章:
- Azure-SSIS IR。 此文提供有关 IR(包括 Azure-SSIS IR)的一般概念性信息。
- 监视 Azure-SSIS IR。 此文介绍如何检索并了解有关 Azure-SSIS IR 的信息。
- 管理 Azure-SSIS IR。 此文介绍如何停止、启动或删除 Azure-SSIS IR。 此外,介绍如何通过添加更多节点来横向扩展 Azure-SSIS IR。
- 在 Azure 中部署、运行和监视 SSIS 包
- 连接到 Azure 中的 SSISDB
- 使用 Windows 身份验证连接到本地数据存储
- 在 Azure 中计划 SSIS 包执行