Skapa en Azure-SSIS-integreringskörning via Azure PowerShell
GÄLLER FÖR: Azure Data Factory Azure Synapse Analytics
Dricks
Prova Data Factory i Microsoft Fabric, en allt-i-ett-analyslösning för företag. Microsoft Fabric omfattar allt från dataflytt till datavetenskap, realtidsanalys, business intelligence och rapportering. Lär dig hur du startar en ny utvärderingsversion kostnadsfritt!
Den här artikeln visar hur du skapar en SSIS-integreringskörning (Azure-SQL Server Integration Services) i Azure Data Factory (ADF) via Azure PowerShell.
Kommentar
För Azure-SSIS IR i Azure Synapse Analytics ersätter du med motsvarande PowerShell-gränssnitt för Azure Synapse Analytics: Set-AzSynapseIntegrationRuntime (Az.Synapse), Start-AzSynapseIntegrationRuntime och Stop-AzSynapseIntegrationRuntime.
Skapa variabler
Kopiera och klistra in följande skript. Ange värden för variablerna.
### 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/?products=data-factory®ions=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®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, 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://learn.microsoft.com/azure/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.windows.net or managed instance name.DNS prefix.database.windows.net or managed instance name.public.DNS prefix.database.windows.net,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://learn.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
Logga in och välj en prenumeration
Lägg till följande skript för att logga in och välj din Azure-prenumeration.
Connect-AzAccount
Select-AzSubscription -SubscriptionName $SubscriptionName
Verifiera anslutningen till databasservern
Lägg till följande skript för att verifiera din Azure SQL Database-server eller hanterade instans.
# 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;
}
}
}
}
}
Konfigurera ett virtuellt nätverk
Lägg till följande skript för att automatiskt konfigurera behörigheter och inställningar för virtuella nätverk för din Azure-SSIS-integreringskörning för anslutning.
# 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
}
}
Skapa en resursgrupp
Skapa en Azure-resursgrupp med kommandot New-AzResourceGroup . En resursgrupp är en logisk container där Azure-resurser distribueras och hanteras som en grupp.
Om resursgruppen redan finns ska du inte kopiera den här koden i skriptet.
New-AzResourceGroup -Location $DataFactoryLocation -Name $ResourceGroupName
Skapa en datafabrik
Skapa en datafabrik genom att köra följande kommandon.
Set-AzDataFactoryV2 -ResourceGroupName $ResourceGroupName `
-Location $DataFactoryLocation `
-Name $DataFactoryName
Skapa Integration Runtime
Kör följande kommandon för att skapa en Azure-SSIS-integreringskörning som kör SSIS-paket i Azure.
Om du inte använder SSISDB kan du utelämna parametrarna CatalogServerEndpoint
, CatalogPricingTier
och CatalogAdminCredential
.
Om du inte använder en Azure SQL Database-server med IP-brandväggsregler/tjänstslutpunkter för virtuella nätverk eller en hanterad instans med en privat slutpunkt som värd för SSISDB, eller kräver åtkomst till lokala data, kan du utelämna parametrarna VNetId
och Subnet
eller skicka tomma värden för dem. Du kan också utelämna dem om du konfigurerar en lokalt installerad IR som proxy för din Azure-SSIS IR för att komma åt data lokalt. Annars kan du inte utelämna dem och måste skicka giltiga värden från konfigurationen av det virtuella nätverket. Mer information finns i Ansluta en Azure-SSIS IR till ett virtuellt nätverk.
Om du använder hanterad instans som värd för SSISDB kan du utelämna parametern CatalogPricingTier
eller skicka ett tomt värde för den. Annars kan du inte utelämna det och måste skicka ett giltigt värde från listan över prisnivåer som stöds för Azure SQL Database. Mer information finns i Resursbegränsningar för SQL Database.
Om du använder Microsoft Entra-autentisering med den angivna system-/användartilldelade hanterade identiteten för din datafabrik för att ansluta till databasservern kan du utelämna parametern CatalogAdminCredential
. Men du måste lägga till den angivna system-/användartilldelade hanterade identiteten för din datafabrik i en Microsoft Entra-grupp med åtkomstbehörigheter till databasservern. Mer information finns i Aktivera Microsoft Entra-autentisering för en Azure-SSIS IR. Annars kan du inte utelämna det och måste skicka ett giltigt objekt som skapats från serveradministratörens användarnamn och lösenord för SQL-autentisering.
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.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 "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
}
}
Starta integrationskörningen
Kör följande kommandon för att starta Azure-SSIS-integreringskörningen.
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.")
Kommentar
Den här processen bör avslutas inom 5 minuter, exklusive eventuell anpassad installationstid. Men det kan ta 20–30 minuter för Azure-SSIS IR att ansluta ett virtuellt nätverk med standardinmatningsmetod.
Om du använder SSISDB ansluter Data Factory-tjänsten till databasservern för att förbereda SSISDB. Den konfigurerar också behörigheter och inställningar för ditt virtuella nätverk, om det anges, och ansluter din Azure-SSIS IR till det virtuella nätverket.
När du etablerar en Azure-SSIS IR installeras även Access Redistributable och Azure Feature Pack for SSIS. Dessa komponenter ger anslutning till Excel-filer, Access-filer och olika Azure-datakällor, utöver de datakällor som inbyggda komponenter redan stöder. Mer information om inbyggda/förinstallerade komponenter finns i Inbyggda/förinstallerade komponenter i Azure-SSIS IR. Mer information om ytterligare komponenter som du kan installera finns i Anpassade installationer för Azure-SSIS IR.
Fullständigt skript
Här är det fullständiga skriptet som skapar en Azure-SSIS-integreringskörning.
### 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/?products=data-factory®ions=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®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, 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://learn.microsoft.com/azure/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.windows.net or managed instance name.DNS prefix.database.windows.net or managed instance name.public.DNS prefix.database.windows.net,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://learn.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 a subscription
Connect-AzAccount
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.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 "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.")
Relaterat innehåll
- Skapa en Azure-SSIS IR via Azure-portalen.
- Skapa en Azure-SSIS IR via Azure Resource Manager-mall.
- Distribuera och kör dina SSIS-paket på Azure-SSIS IR.
Mer information om Azure-SSIS IR finns i följande artiklar:
- Azure-SSIS IR. Den här artikeln innehåller allmän konceptuell information om IR:er, inklusive Azure-SSIS IR.
- Övervaka en Azure-SSIS IR. Den här artikeln visar hur du hämtar och förstår information om din Azure-SSIS IR.
- Hantera en Azure-SSIS IR. Den här artikeln visar hur du stoppar, startar eller tar bort din Azure-SSIS IR. Den visar också hur du skalar ut Azure-SSIS IR genom att lägga till fler noder.
- Distribuera, köra och övervaka SSIS-paket i Azure
- Ansluta till SSISDB i Azure
- Ansluta till lokala datalager med Windows-autentisering
- Schemalägga SSIS-paketkörningar i Azure