I just created an Azure Data Factory V2 to start with SSIS in the cloud, but I cannot find the SSIS options in ADF. How do I configure SSIS in Azure?
 |
| Azure Data Factory V2 |
Solution
At the moment SSIS (ADF V2) is still in the preview. The user interface for SSIS is not yet available in the Azure portal (however it is visible in this video, but probably only for Microsoft).
For now you are stuck with a little PowerShell scripting. Before you start make sure you have an ADF V2 and an Azure Database Server available. Preferably in the same Azure region. ADF V2 is only available in East US, East US2 and West Europe. Since I'm from the Netherlands I selected West Europe.
PowerShell ISE
If you never used PowerShell for Azure code then you first need to start Windows PowerShell ISE as Administrator to install the Azure Module. Execute the following command:
# PowerShell code Install-Module AzureRM
And if you did work with Azure PowerShell before then you probably have to update the Azure Module by adding the parameter -Force at the end
 |
| Install-Module AzureRM |
Parameters
This PowerShell script first starts with a 'parameter' section to provide all details necessary for the script to run.
First start with the name of your subscription. If you are not sure which one it is then you can look it up in the overview page of your Azure Data Factory under Subscription name.
 |
| Subscription name in ADF overview page |
################################################# ################## PARAMETERS ################### ################################################# $SubscriptionName = "mySubscriptionName"
To store the SSISDB we need to provide a database server URL and the server admin and its password. The URL can be found on the SQL database overview page and the user can be found on the SQL server overview page.
#Provide login details for the existing database server $CatalogServerEndpoint = "myDBServer.database.windows.net" $DBUser = "Joost" $DBPassword = "5ecret!
Next we need to provide the details about our newly created Azure Data Factory V2 environment. You can find all the required information on the overview page of ADF. The location is either WestEurope or EastUs or EastUs2.
 |
| Azure Data Factory V2 overview page |
# Provide details about your existing ADF V2 $DataFactoryName = "bitools" $ResourceGroupName = "Joost_van_Rossum" $Location = "WestEurope"
The last part is to configure the Integration Runtime. The Catalog Pricing Tier is the database size of your SSISDB database. It should be Basic or S1, S2, S3... S12, etc. The node size should be Standard_A4_v2, Standard_A8_v2, Standard_D1_v2, Standard_D2_v2, Standard_D3_v2 or Standard_D4_v2. After the preview phase more sizes will be available. Prices can be found here.
# Provide details for the new Integration Runtime $IntegrationRuntimeName = "SSISJoostIR" $IntegrationRuntimeDescription = "My First Azure Integration Catalog" $CatalogPricingTier = "Basic" # S0, S1, S2, S3 $IntegrationRuntimeNodeSize = "Standard_A4_v2" $IntegrationRuntimeNodeCount = 2 $IntegrationRuntimeParallelExecutions = 2
The script
And now the script itself. It starts with a setting to stop after an error (not the default setting) and a login to Azure. When executing Login-AzureRmAccount it will show a login popup. Login with your azure account.
 |
| Login to Azure |
################################################# ################## THE SCRIPT ################### ################################################# $ErrorActionPreference = "Stop" # Login to Azure (a pop-up will appear) Login-AzureRmAccount
After the login we need to select the right subscription. The Out-Null will prevent showing all properties of your subscription to the screen.
# Select the right subscription Select-AzureRmSubscription -SubscriptionName $SubscriptionName | Out-Null
And this will create a new credential with the user id and password. We need it for the next command.
# Create Database creditial with user id and password $SecureDBPassword = ConvertTo-SecureString $DBPassword -AsPlainText -Force $ServerCreds = New-Object System.Management.Automation.PSCredential($DBUser, $SecureDBPassword)
This command will create the Integration Runtime environment in Azure Data Factory.
# Create the Integration Runtime
Write-Host "Creating your integration runtime."
Set-AzureRmDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
-DataFactoryName $DataFactoryName `
-Name $IntegrationRuntimeName `
-Type Managed `
-CatalogServerEndpoint $CatalogServerEndpoint `
-CatalogAdminCredential $ServerCreds `
-CatalogPricingTier $CatalogPricingTier `
-Description $IntegrationRuntimeDescription `
-Location $Location `
-NodeSize $IntegrationRuntimeNodeSize `
-NodeCount $IntegrationRuntimeNodeCount `
-MaxParallelExecutionsPerNode $IntegrationRuntimeParallelExecutions
After creating the Integration Runtime environment we need to start it. Only then you can use it. Starting the environment takes 20 to 30 minutes! There is also an Stop-AzureRmDataFactoryV2IntegrationRuntime method which takes the same parameters and takes 2 to 3 minutes.
# Start the Integration Runtime (takes 20 to 30 minutes)
Write-Warning "Starting your integration runtime. This command takes 20 to 30 minutes to complete."
Start-AzureRmDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
-DataFactoryName $DataFactoryName `
-Name $IntegrationRuntimeName `
-Force
Connecting with SSMS
Now we can use SQL Server Management Studio (SSMS) to connect to our newly created SSISDB in Azure. A little different compared to on-premises: you need to click on the Options button and select the SSISDB first. Otherwise you won't see the Integration Services Catalog.
 |
| Connecting to the SSISDB in Azure |
 |
| Spot the differences |
In the next post I will show the deployment to the Integration Services Catalog in Azure. And now the complete script for copy and paste purposes.
#################################################
################## PARAMETERS ###################
#################################################
$SubscriptionName = "mySubscriptionName"
# Provide login details for the existing database server
$CatalogServerEndpoint = "myDBServer.database.windows.net"
$DBUser = "Joost"
$DBPassword = "5ecret!"
# Provide details about your existing ADF V2
$DataFactoryName = "bitools"
$ResourceGroupName = "Joost_van_Rossum"
$Location = "WestEurope" # or EastUs/EastUs2
# Provide details for the new Integration Runtime
$IntegrationRuntimeName = "SSISJoostIR"
$IntegrationRuntimeDescription = "My First Azure Integration Catalog"
$CatalogPricingTier = "Basic" # S0, S1, S2, S3
$IntegrationRuntimeNodeSize = "Standard_A4_v2"
$IntegrationRuntimeNodeCount = 2
$IntegrationRuntimeParallelExecutions = 2
# In public preview, only Standard_A4_v2, Standard_A8_v2, Standard_D1_v2,
# Standard_D2_v2, Standard_D3_v2, Standard_D4_v2 are supported.
#################################################
################## THE SCRIPT ###################
#################################################
$ErrorActionPreference = "Stop"
# Login to Azure (a pop-up will appear)
Login-AzureRmAccount
# Select the right subscription
Select-AzureRmSubscription -SubscriptionName $SubscriptionName | Out-Null
# Create Database creditial with user id and password
$SecureDBPassword = ConvertTo-SecureString $DBPassword -AsPlainText -Force
$ServerCreds = New-Object System.Management.Automation.PSCredential($DBUser, $SecureDBPassword)
# Create the Integration Runtime
Write-Host "Creating your integration runtime."
Set-AzureRmDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
-DataFactoryName $DataFactoryName `
-Name $IntegrationRuntimeName `
-Type Managed `
-CatalogServerEndpoint $CatalogServerEndpoint `
-CatalogAdminCredential $ServerCreds `
-CatalogPricingTier $CatalogPricingTier `
-Description $IntegrationRuntimeDescription `
-Location $Location `
-NodeSize $IntegrationRuntimeNodeSize `
-NodeCount $IntegrationRuntimeNodeCount `
-MaxParallelExecutionsPerNode $IntegrationRuntimeParallelExecutions
# Start the Integration Runtime (takes 20 to 30 minutes)
Write-Warning "Starting your integration runtime. This command takes 20 to 30 minutes to complete."
Start-AzureRmDataFactoryV2IntegrationRuntime -ResourceGroupName $ResourceGroupName `
-DataFactoryName $DataFactoryName `
-Name $IntegrationRuntimeName `
-Force
Write-Host "Done"
Note: when you get the error message "There is no active worker agent" while executing an SSIS package then you probably need to start the Integration Runtime (see Start-AzureRmDataFactoryV2IntegrationRuntime).
 |
| There is no active worker agent. |
No comments:
Post a Comment
Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.
All comments are moderated manually to prevent spam.