PowerShell script - deploy Azure-SSIS integration runtime
This sample PowerShell script creates an Azure-SSIS integration runtime that can run your SSIS packages in Azure.
Note
We recommend that you use the Azure Az PowerShell module to interact with Azure. To get started, see Install Azure PowerShell. To learn how to migrate to the Az PowerShell module, see Migrate Azure PowerShell from AzureRM to Az.
This sample requires Azure PowerShell. Run Get-Module -ListAvailable Az to find the version.
If you need to install or upgrade, see Install Azure PowerShell module.
Run the Connect-AzAccount cmdlet to connect to Azure.
Sample script
# If your inputs contain PSH special characters, e.g. "$", please precede it with the escape character "`" like "`$".
# Azure Data Factory v2 information
$SubscriptionName = "[your Azure subscription name]"
$ResourceGroupName = "[your Azure resource group name]"
$DataFactoryName = "[your data factory name]"
$DataFactoryLocation = "EastUS" # In Public Preview, only EastUS|EastUS2 are supported for now
# Azure-SSIS Integration Runtime info - This is Data Factory compute resource for running SSIS packages
$AzureSSISName = "[your Azure-SSIS Integration Runtime name]"
$AzureSSISDescription = "This is my Azure-SSIS Integration Runtime"
$AzureSSISLocation = "EastUS" # In Public Preview, only EastUS|NorthEurope are supported for now
$AzureSSISNodeSize = "Standard_A4_v2" # In Public Preview, only Standard_A4_v2|Standard_A8_v2|Standard_D1_v2|Standard_D2_v2|Standard_D3_v2|Standard_D4_v2 are supported for now
$AzureSSISNodeNumber = 2 # In Public Preview, only 1-10 nodes are supported for now
$AzureSSISMaxParallelExecutionsPerNode = 2 # In Public Preview, only 1-8 parallel executions per node are supported for now
$VnetId = "[your VNet resource ID or leave it empty]" # OPTIONAL: In Public Preview, only Classic VNet is supported for now
$SubnetName = "[your subnet name or leave it empty]" # OPTIONAL: In Public Preview, only Classic VNet is supported for now
# SSISDB info
$SSISDBServerEndpoint = "[your Azure SQL Database server name.database.windows.net or your Azure SQL Managed Instance server endpoint]"
$SSISDBServerAdminUserName = "[your server admin username]"
$SSISDBServerAdminPassword = "[your server admin password]"
$SSISDBPricingTier = "[your Azure SQL Database pricing tier, e.g. S3, or leave it empty for Azure SQL Managed Instance]" # Not applicable for Azure SQL Managed Instance
##### Validate your Azure SQL Database/Managed Instance server #####
$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 DB logical server/Azure SQL MI 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;
}
}
##### Login and and select your Azure subscription #####
Login-AzAccount
Select-AzSubscription -SubscriptionName $SubscriptionName
##### Automatically configure VNet permissions/settings for your Azure-SSIS Integration Runtime to join #####
# Register to Azure Batch resource provider
if(![string]::IsNullOrEmpty($VnetId) -and ![string]::IsNullOrEmpty($SubnetName))
{
$BatchObjectId = (Get-AzADServicePrincipal -ServicePrincipalName "MicrosoftAzureBatch").Id
Register-AzResourceProvider -ProviderNamespace Microsoft.Batch
while(!(Get-AzResourceProvider -ProviderNamespace "Microsoft.Batch").RegistrationState.Contains("Registered"))
{
Start-Sleep -s 10
}
# Assign VM contributor role to Microsoft.Batch
New-AzRoleAssignment -ObjectId $BatchObjectId -RoleDefinitionName "Classic Virtual Machine Contributor" -Scope $VnetId
}
##### Provision your Azure Data Factory + Azure-SSIS Integration Runtime #####
New-AzResourceGroup -Location $DataFactoryLocation -Name $ResourceGroupName
Register-AzResourceProvider -ProviderNamespace Microsoft.DataFactory
$secpasswd = ConvertTo-SecureString $SSISDBServerAdminPassword -AsPlainText -Force
$serverCreds = New-Object System.Management.Automation.PSCredential($SSISDBServerAdminUserName, $secpasswd)
Set-AzDataFactory -ResourceGroupName $ResourceGroupName `
-Location $DataFactoryLocation `
-Name $DataFactoryName
Set-AzDataFactoryIntegrationRuntime -ResourceGroupName $ResourceGroupName `
-DataFactoryName $DataFactoryName `
-Name $AzureSSISName `
-Type Managed `
-CatalogServerEndpoint $SSISDBServerEndpoint `
-CatalogAdminCredential $serverCreds `
-CatalogPricingTier $SSISDBPricingTier `
-Description $AzureSSISDescription `
-Location $AzureSSISLocation `
-NodeSize $AzureSSISNodeSize `
-NodeCount $AzureSSISNodeNumber `
-MaxParallelExecutionsPerNode $AzureSSISMaxParallelExecutionsPerNode `
-VnetId $VnetId `
-Subnet $SubnetName
write-host("##### Starting #####")
Start-AzDataFactoryIntegrationRuntime -ResourceGroupName $ResourceGroupName `
-DataFactoryName $DataFactoryName `
-Name $AzureSSISName `
-Force
write-host("##### Completed #####")
write-host("If any cmdlet is unsuccessful, please consider using -Debug option for diagnostics.")
##### Query/monitor your Azure-SSIS Integration Runtime #####
#Get-AzDataFactoryIntegrationRuntime -DataFactoryName $DataFactoryName -Name $AzureSSISName -ResourceGroupName $ResourceGroupName -Status
##### Reconfigure your Azure-SSIS Integration Runtime, e.g. stopping/scaling out to 5 nodes/starting #####
# Stopping your Azure-SSIS Integration Runtime will release its nodes and stop billing
#Stop-AzDataFactoryIntegrationRuntime -DataFactoryName $DataFactoryName -Name $AzureSSISName -ResourceGroupName $ResourceGroupName
# Scaling out your Azure-SSIS Integration Runtime to 5 nodes
#Set-AzDataFactoryIntegrationRuntime -DataFactoryName $DataFactoryName -Name $AzureSSISName -ResourceGroupName $ResourceGroupName -NodeCount 5
# Starting your Azure-SSIS Integration Runtime will allocate its nodes and start billing
#Start-AzDataFactoryIntegrationRuntime -DataFactoryName $DataFactoryName -Name $AzureSSISName -ResourceGroupName $ResourceGroupName
##### Clean up ######
#Stop-AzDataFactoryIntegrationRuntime -DataFactoryName $DataFactoryName -Name $AzureSSISName -ResourceGroupName $ResourceGroupName -Force
#Remove-AzDataFactoryIntegrationRuntime -DataFactoryName $DataFactoryName -Name $AzureSSISName -ResourceGroupName $ResourceGroupName -Force
#Remove-AzDataFactory -Name $DataFactoryName -ResourceGroupName $ResourceGroupName -Force
#Remove-AzResourceGroup -Name $ResourceGroupName -Force
Clean up deployment
After you run the sample script, you can use the following command to remove the resource group and all resources associated with it:
Remove-AzResourceGroup -ResourceGroupName $resourceGroupName
To remove the data factory from the resource group, run the following command:
Remove-AzDataFactoryV2 -Name $dataFactoryName -ResourceGroupName $resourceGroupName
Script explanation
This script uses the following commands:
| Command | Notes |
|---|---|
| New-AzResourceGroup | Creates a resource group in which all resources are stored. |
| Set-AzDataFactoryV2 | Create a data factory. |
| Set-AzDataFactoryV2IntegrationRuntime | Creates an Azure-SSIS integration runtime that can run SSIS packages in the cloud |
| Start-AzDataFactoryV2IntegrationRuntime | Starts the Azure-SSIS integration runtime. |
| Get-AzDataFactoryV2IntegrationRuntime | Gets information about the Azure-SSIS integration runtime. |
| Remove-AzResourceGroup | Deletes a resource group including all nested resources. |
Related content
For more information on the Azure PowerShell, see Azure PowerShell documentation.
Additional Azure Data Factory PowerShell script samples can be found in the Azure Data Factory PowerShell samples.