使用 PowerShell 在 Azure 数据工厂中设置 Azure-SSIS IR
适用于: Azure 数据工厂 Azure Synapse Analytics
提示
试用 Microsoft Fabric 中的数据工厂,这是一种适用于企业的一站式分析解决方案。 Microsoft Fabric 涵盖从数据移动到数据科学、实时分析、商业智能和报告的所有内容。 了解如何免费开始新的试用!
本教程提供使用 PowerShell 在 Azure 数据工厂 (ADF) 中预配 Azure-SQL Server Integration Services (SSIS) Integration Runtime (IR) 的步骤。 Azure-SSIS IR 支持:
- 运行部署在由 Azure SQL 数据库服务器/托管实例托管的 SSIS 目录 (SSISDB) 中的包(项目部署模型)
- 运行部署在由 Azure SQL 托管实例托管的文件系统、Azure 文件存储或 SQL Server 数据库 (MSDB) 中的包(包部署模型)
预配 Azure-SSIS IR 后,可以使用熟悉的工具在 Azure 中部署和运行包。 这些工具包括 SQL Server Management Studio (SSMS) 和命令行实用工具,例如 dtutil 和 AzureDTExec。
有关 Azure-SSIS IR 的概念性信息,请参阅 Azure-SSIS 集成运行时概述。
注意
本文演示如何使用 Azure PowerShell 设置 Azure-SSIS IR。 若要使用 Azure 门户或 Azure 数据工厂应用来设置 Azure-SSIS IR,请参阅教程:设置 Azure-SSIS IR。
在本教程中,你将:
- 创建数据工厂。
- 创建 Azure SSIS Integration Runtime。
- 启动 Azure-SSIS Integration Runtime。
- 查看完整脚本。
- 部署 SSIS 包。
先决条件
注意
建议使用 Azure Az PowerShell 模块与 Azure 交互。 请参阅安装 Azure PowerShell 以开始使用。 若要了解如何迁移到 Az PowerShell 模块,请参阅 将 Azure PowerShell 从 AzureRM 迁移到 Az。
Azure 订阅。 如果没有 Azure 订阅,可在开始前创建一个试用帐户。
Azure SQL 数据库服务器或托管实例(可选) 。 如果还没有数据库服务器,请在启动之前在 Azure 门户中创建一个。 数据工厂进而会在此数据库服务器上创建一个 SSISDB 实例。
建议在集成运行时所在的同一 Azure 区域中创建数据库服务器。 此配置允许集成运行时将执行日志写入 SSISDB 而无需跨 Azure 区域。
请记住以下几点:
根据所选的数据库服务器,系统可以代表你创建 SSISDB 实例作为单一数据库、创建此实例作为弹性池的一部分,或者在托管实例中创建。 可以在公用网络中访问或者通过加入虚拟网络来访问该实例。 有关选择用来托管 SSISDB 的数据库服务器类型的指导,请参阅比较 SQL 数据库与 SQL 托管实例。
如果使用具有 IP 防火墙规则/虚拟网络服务终结点的 Azure SQL 数据库服务器或具有专用终结点的托管实例来承载 SSISDB,或者需要在未配置自承载 IR 的情况下访问本地数据,则需要将 Azure-SSIS IR 加入虚拟网络。 有关详细信息,请参阅在虚拟网络中创建 Azure-SSIS IR。
确认为数据库服务器启用了“允许访问 Azure 服务”设置。 使用具有 IP 防火墙规则/虚拟网络服务终结点的 Azure SQL 数据库服务器或具有专用终结点的托管实例来承载 SSISDB 时,此设置并不适用。 有关详细信息,请参阅保护 Azure SQL 数据库的安全。 若要通过 PowerShell 来启用此设置,请参阅 New-AzSqlServerFirewallRule。
将客户端计算机的 IP 地址或一系列包括客户端计算机 IP 地址的 IP 地址添加到数据库服务器的防火墙设置中的客户端 IP 地址列表。 有关详细信息,请参阅 Azure SQL 数据库服务器级和数据库级防火墙规则。
若要连接到数据库服务器,可以通过使用 SQL 身份验证(使用服务器管理员凭据)的方式,也可以通过使用 Microsoft Entra 身份验证(使用为数据工厂指定的系统/用户分配的托管标识)的方式。 对于后一种方式,需要将为数据工厂指定的系统/用户分配的托管标识添加到有权访问数据库服务器的 Microsoft Entra 组中。 有关详细信息,请参阅使用 Microsoft Entra 身份验证创建 Azure-SSIS IR。
确认你的数据库服务器还没有 SSISDB 实例。 预配 Azure-SSIS IR 时不支持使用现有的 SSISDB 实例。
Azure PowerShell。 若要运行 PowerShell 脚本来设置 Azure-SSIS IR,请按照安装和配置 Azure PowerShell 中的说明操作。
注意
有关目前提供 Azure 数据工厂和 Azure-SSIS IR 的 Azure 区域列表,请参阅 Azure 数据工厂和 Azure-SSIS IR 在各区域的上市情况。
打开 Windows PowerShell ISE
以管理员权限打开 Windows PowerShell 集成脚本环境 (ISE)。
创建变量
将以下脚本复制到 ISE 中。 指定变量的值。
### 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/?regions=china-non-regional,china-east,china-east-2,china-north,china-north-2&products=all
$DataFactoryLocation = "ChinaEast 2"
### 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=all
$AzureSSISLocation = "China East 2"
# For supported node sizes, see https://azure.cn/pricing/details/data-factory/
$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
# 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
### SSISDB info
$SSISDBServerEndpoint = "[your server name.database.chinacloudapi.cn or managed instance name.public.DNS prefix.database.chinacloudapi.cn,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.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 SQL Database or leave it empty for SQL 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
验证与数据库服务器的连接
若要验证连接,请添加以下脚本:
# 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, 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 SQL 数据库实例,请参阅以下示例。 设置尚未定义的变量(例如 SSISDBServerName、FirewallIPAddress)的值。
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
创建资源组
使用 New-AzResourceGroup 命令创建 Azure 资源组。 资源组是在其中以组的形式部署和管理 Azure 资源的逻辑容器。
如果资源组已存在,请不要将此代码复制到脚本中。
New-AzResourceGroup -Location $DataFactoryLocation -Name $ResourceGroupName
创建数据工厂
运行以下命令以创建数据工厂:
Set-AzDataFactoryV2 -ResourceGroupName $ResourceGroupName `
-Location $DataFactoryLocation `
-Name $DataFactoryName
创建 Azure SSIS Integration Runtime
若要在 Azure 中创建运行 SSIS 包的 Azure-SSIS Integration Runtime,请运行以下命令。 如果不使用 SSISDB,则可以省略 CatalogServerEndpoint、CatalogPricingTier 和 CatalogAdminCredential 参数。
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.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
}
}
启动 Azure-SSIS Integration Runtime
若要启动 Azure-SSIS IR,请运行以下命令:
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 分钟内完成(不包括任何自定义安装时间)。
如果使用 SSISDB,数据工厂服务将连接到数据库服务器以准备 SSISDB。
预配 Azure-SSIS IR 时,还会安装 Access Redistributable 和 Azure Feature Pack for SSIS。 除了内置组件已支持的数据源外,这些组件还提供与 Excel 文件、Access 文件和各种 Azure 数据源的连接。 有关内置/已预安装组件的详细信息,请参阅 Azure-SSIS IR 上的内置/已预安装组件。 有关可安装的其他组件的详细信息,请参阅 Azure-SSIS IR 的自定义安装。
完整脚本
本部分中的 PowerShell 脚本配置运行 SSIS 包的 Azure-SSIS IR 实例。 成功运行此脚本后,可以在 Azure 中部署和运行 SSIS 包。
打开 ISE。
在 ISE 命令提示符下,运行以下命令:
Set-ExecutionPolicy Unrestricted -Scope CurrentUser
将本部分中的 PowerShell 脚本复制到 ISE 中。
为脚本开头的所有参数提供适当的值。
运行该脚本。
### 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/?regions=china-non-regional,china-east,china-east-2,china-north,china-north-2&products=all
$DataFactoryLocation = "ChinaEast 2"
### 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=all
$AzureSSISLocation = "China East 2"
# For supported node sizes, see https://azure.cn/pricing/details/data-factory/
$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 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 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|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
### SSISDB info
$SSISDBServerEndpoint = "[your server name.database.chinacloudapi.cn or managed instance name.public.DNS prefix.database.chinacloudapi.cn,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.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 SQL Database or leave it empty for SQL 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 -Environment AzureChinaCloud
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, 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.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
}
}
### 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.")
监视和管理 Azure-SSIS IR
有关监视和管理 Azure-SSIS IR 的信息,请参阅:
部署 SSIS 包
如果使用 SSISDB,可将包部署到其中,并使用 SSMS 工具在 Azure-SSIS IR 上运行这些包。 这些工具通过数据库服务器的服务器终结点来与该服务器建立连接:
- 对于 Azure SQL 数据库服务器,服务器终结点格式为
<server name>.database.chinacloudapi.cn
。 - 对于具有专用终结点的托管实例,服务器终结点格式为
<server name>.<dns prefix>.database.chinacloudapi.cn
。 - 对于具有公共终结点的托管实例,服务器终结点格式为
<server name>.public.<dns prefix>.database.chinacloudapi.cn,3342
。
如果不使用 SSISDB,则可以将包部署到由 Azure SQL 托管实例托管的文件系统、Azure 文件存储或 MSDB 中,并使用 dtutil 和 AzureDTExec 命令行实用工具在 Azure-SSIS IR 上运行它们。
有关详细信息,请参阅部署 SSIS 项目/包。
在这两种情况下,还可以使用数据工厂管道中的“执行 SSIS 包”活动在 Azure-SSIS IR 上运行已部署的包。 有关详细信息,请参阅以第一类数据工厂活动的形式调用 SSIS 包执行。
有关更多 SSIS 文档,请参阅:
相关内容
在本教程中,你了解了如何执行以下操作:
- 创建数据工厂。
- 创建 Azure SSIS Integration Runtime。
- 启动 Azure-SSIS Integration Runtime。
- 查看完整脚本。
- 部署 SSIS 包。
若要了解如何自定义 Azure-SSIS Integration Runtime,请参阅下文: