Sunday 17 December 2017

Deploying to Azure Integration Services Preview (ADF V2)

Case
I just created an Integration Services Catalog in Azure Data Factory V2, but how do I deploy SSIS packages to this new catalog in Azure.
Azure Integration Services























Solution
The Integration Services catalog in Azure doesn't support Windows Authentication like the on premises version. Therefore the PowerShell deployment script and the deployment in SSDT won't work without some changes.
For this example I will focus on the SQL Server Authentication for which you can use the same user that you used to configure the catalog in Azure or create a new SQL user.

Solution 1: SSDT
First make sure you have the latest version of SSDT 2015 (17.4 or higher) or SSDT 2017 (15.5.0 or higher). If you already have SSDT 2017 make sure you first remove any installed Visual Studio extension of SSRS or SSAS projects before installing SSDT. For downloads and more details see this page.

If you use an older version that doesn't have the option for SQL Server Authentication you will get an error: Failed to connect to server bitools2.database.windows.net. (Microsoft.SqlServer.ConnectionInfo)
Windows logins are not supported in this version of SQL Server. (Microsoft SQL Server, Error: 40607)

Windows logins are not supported
in this version of SQL Server.

























After updating SSDT you will see a new Integration Services Deployment Wizard, which supports three new authentication methods. After filling in the Server name (which is the URL of your Azure SQL server that hosts your SSISDB), choose the SQL Server Authentication method. Fill in the username and password and click on the Connect button. After that you can use the Browse button to browse your Integration Services catalog in Azure.
New Integration Services Deployment Wizard

























Solution 2: PowerShell
If you regularly deploy SSIS projects you probably use a PowerShell script. The 'old' deployment scripts uses Windows Authentication to create a 'System.Data.SqlClient.SqlConnection' connection to the master database on the server that hosts the SSISDB. See this snippet:
# PowerShell code snippet for on premises deployment
#################################################
############ CONNECT TO SSIS SERVER #############
#################################################
# First create a connection to SQL Server
$SqlConnectionstring = "Data Source=$($SsisServer);Initial Catalog=master;Integrated Security=SSPI;"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnectionstring

We need to change two things. First we need to change the connectionstring to SQL Server Authentication by adding a username and password and removing 'Integrated Security=SSPI;'. Secondly we need to change the database in the Initial Catalog part. This should be SSISDB instead of master.
# PowerShell code snippet for on premises deployment
#################################################
##################### SERVER ####################
#################################################
Write-Host "Connecting to Azure SQL DB server $($SsisServer)"
# Create a connectionstring for the Azure DB Server
# Make sure you use SSISDB as the Initial Catalog!
$SqlConnectionstring = "Data Source=$($SsisServer);User ID=$($SSISDBUsername);Password=$($SSISDBPassword);Initial Catalog=SSISDB;"


Selecting the SSISDB database also applies when you want to use SSMS to connect to your catalog in Azure. Below the complete script for deploying ISPAC files. The solution contains two files. The first is the file with all the parameters. This changes per project. The last line executes the second script.
# PowerShell code snippet for on premises deployment
#PowerShell: finance.ps1
#################################################################################################
# Change source, destination and environment properties
#################################################################################################
 
# Source
$IspacFilePath = "d:\projects\Finance\bin\Development\Finance.ispac"
 
# Destination
$SsisServer ="bitools2.database.windows.net"
$SSISDBUsername = "Joost"
$SSISDBPassword = "5ecrtet"
$FolderName = "Finance"
$ProjectName = ""
 
# Environment
$EnvironmentName = "Generic"
$EnvironmentFolderName = "Environments"
 
#################################################################################################
# Execute generic deployment script
. "$PSScriptRoot\generalAzureDeployment.ps1" $IspacFilePath $SsisServer $SSISDBUsername $SSISDBPassword $FolderName $ProjectName $EnvironmentName $EnvironmentFolderName

The second file is the generic scripts which will be the same for each project. If you want to make any changes to the script you now only need to maintain one generic PowerShell scripts instead of dozens of copies for each project.

# PowerShell code snippet for on premises deployment
#PowerShell: generalAzureDeployment.ps1
################################
########## PARAMETERS ##########
################################ 
[CmdletBinding()]
Param(
    # IsPacFilePath is required
    [Parameter(Mandatory=$True,Position=1)]
    [string]$IspacFilePath,
     
    # SsisServer is required 
    [Parameter(Mandatory=$True,Position=2)]
    [string]$SsisServer,

    # SSISDB Username is required 
    [Parameter(Mandatory=$True,Position=3)]
    [string]$SSISDBUsername,

    # SSISDB Password is required 
    [Parameter(Mandatory=$True,Position=4)]
    [string]$SSISDBPassword,
     
    # FolderName is required
    [Parameter(Mandatory=$True,Position=5)]
    [string]$FolderName,
     
    # ProjectName is not required
    # If empty filename is used
    [Parameter(Mandatory=$False,Position=6)]
    [string]$ProjectName,
     
    # EnvironmentName is not required
    # If empty no environment is referenced
    [Parameter(Mandatory=$False,Position=7)]
    [string]$EnvironmentName,
     
    # EnvironmentFolderName is not required
    # If empty the FolderName param is used
    [Parameter(Mandatory=$False,Position=8)]
    [string]$EnvironmentFolderName
)
 
# Replace empty projectname with filename
if (-not $ProjectName)
{
  $ProjectName = [system.io.path]::GetFileNameWithoutExtension($IspacFilePath)
}
# Replace empty Environment folder with project folder
if (-not $EnvironmentFolderName)
{
  $EnvironmentFolderName = $FolderName
}

# Mask the password to show something on
# screen, but not the actual password
# This is for testing purposes only.
$SSISDBPasswordMask = $SSISDBPassword -replace '.', '*'

clear
Write-Host "========================================================================================================================================================"
Write-Host "==                                                         Used parameters                                                                            =="
Write-Host "========================================================================================================================================================"
Write-Host "Ispac File Path        : " $IspacFilePath
Write-Host "SSIS Server            : " $SsisServer
Write-Host "SQL Username           : " $SSISDBUsername
Write-Host "SQL Password           : " $SSISDBPasswordMask 
Write-Host "Project Folder Path    : " $FolderName
Write-Host "Project Name           : " $ProjectName
Write-Host "Environment Name       : " $EnvironmentName
Write-Host "Environment Folder Path: " $EnvironmentFolderName
Write-Host "========================================================================================================================================================"
Write-Host ""
 
# Stop the script if an error occurs
$ErrorActionPreference = "Stop"

#################################################
##################### ISPAC #####################
#################################################
# Check if ispac file exists
if (-Not (Test-Path $IspacFilePath))
{
    Throw  [System.IO.FileNotFoundException] "Ispac file $IspacFilePath doesn't exists!"
}
else
{
    $IspacFileName = split-path $IspacFilePath -leaf
    Write-Host "Ispac file" $IspacFileName "found"
}



#################################################
############### ADD SSIS ASSEMBLY ###############
#################################################
# Add SSIS assembly so you can do SSIS stuff in PowerShell
# The number 14.0.0.0 refers to SQL Server 2017
# 13.0.0.0 to SQL Server 2016, 12.0.0.0 to SQL
# Server 2014 and 11.0.0.0 to SQL Server 2012
$SsisNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
Add-Type -AssemblyName "$($SsisNamespace), Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" 



#################################################
##################### SERVER ####################
#################################################
Write-Host "Connecting to Azure SQL DB server $($SsisServer)"
 
# Create a connectionstring for the Azure DB Server
# Make sure you use SSISDB as the Initial Catalog!
$SqlConnectionstring = "Data Source=$($SsisServer);User ID=$($SSISDBUsername);Password=$($SSISDBPassword);Initial Catalog=SSISDB;"

# Create a connection object
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnectionstring

# Check if the connection works
Try
{
    $SqlConnection.Open();
    Write-Host "Connected to Azure SQL DB server $($SsisServer)"
}
Catch [System.Data.SqlClient.SqlException]
{
    Throw  [System.Exception] "Failed to connect to Azure SQL DB server $($SsisServer), exception: $($_)"
}

# Create the Integration Services object
$IntegrationServices = New-Object $SsisNamespace".IntegrationServices" $SqlConnection
 
# Check if SSISDB connection succeeded
if (-not $IntegrationServices)
{
  Throw  [System.Exception] "Failed to connect to SSISDB on $($SsisServer)"
}
else
{
   Write-Host "Connected to SSISDB on $($SsisServer)"
}



#################################################
#################### CATALOG ####################
#################################################
# Create object for SSISDB Catalog
$Catalog = $IntegrationServices.Catalogs["SSISDB"]
 
# Check if the SSISDB Catalog exists
if (-not $Catalog)
{
    # Catalog doesn't exists. The user should create it manually.
    # It is possible to create it, but that shouldn't be part of
    # deployment of packages.
    # Also make sure the catalog is SSISDB and not master or any
    # other database.
    Throw  [System.Exception] "SSISDB catalog doesn't exist. Create it manually!"
}
else
{
    Write-Host "Catalog SSISDB found"
}

 
 
#################################################
#################### FOLDER #####################
#################################################
# Create object to the (new) folder
$Folder = $Catalog.Folders[$FolderName]
 
# Check if folder already exists
if (-not $Folder)
{
    # Folder doesn't exists, so create the new folder.
    Write-Host "Creating new folder" $FolderName
    $Folder = New-Object $SsisNamespace".CatalogFolder" ($Catalog, $FolderName, $FolderName)
    $Folder.Create()
}
else
{
    Write-Host "Folder" $FolderName "found"
}


 
#################################################
#################### PROJECT ####################
#################################################
# Deploying project to folder
if($Folder.Projects.Contains($ProjectName)) {
    Write-Host "Deploying" $ProjectName "to" $FolderName "(REPLACE)"
}
else
{
    Write-Host "Deploying" $ProjectName "to" $FolderName "(NEW)"
}
# Reading ispac file as binary
[byte[]] $IspacFile = [System.IO.File]::ReadAllBytes($IspacFilePath) 
$Folder.DeployProject($ProjectName, $IspacFile) | Out-Null 
$Project = $Folder.Projects[$ProjectName]
if (-not $Project)
{
    # Something went wrong with the deployment
    # Don't continue with the rest of the script
    return ""
}



#################################################
################## ENVIRONMENT ##################
#################################################
# Check if environment name is filled
if (-not $EnvironmentName)
{
    # Kill connection to SSIS
    $IntegrationServices = $null
 
    # Stop the deployment script
    Return "Ready deploying $IspacFileName without adding environment references"
}
 
# Create object to the (new) folder
$EnvironmentFolder = $Catalog.Folders[$EnvironmentFolderName]
 
# Check if environment folder exists
if (-not $EnvironmentFolder)
{
  Throw  [System.Exception] "Environment folder $EnvironmentFolderName doesn't exist"
}
 
# Check if environment exists
if(-not $EnvironmentFolder.Environments.Contains($EnvironmentName))
{
  Throw  [System.Exception] "Environment $EnvironmentName doesn't exist in $EnvironmentFolderName "
}
else
{
    # Create object for the environment
    $Environment = $Catalog.Folders[$EnvironmentFolderName].Environments[$EnvironmentName]
 
    if ($Project.References.Contains($EnvironmentName, $EnvironmentFolderName))
    {
        Write-Host "Reference to" $EnvironmentName "found"
    }
    else
    {
        Write-Host "Adding reference to" $EnvironmentName
        $Project.References.Add($EnvironmentName, $EnvironmentFolderName)
        $Project.Alter() 
    }
}


 
#################################################
############## PROJECT PARAMETERS ###############
#################################################
$ParameterCount = 0
# Loop through all project parameters
foreach ($Parameter in $Project.Parameters)
{
    # Get parameter name and check if it exists in the environment
    $ParameterName = $Parameter.Name
    if ($ParameterName.StartsWith("CM.","CurrentCultureIgnoreCase"))
    {
        # Ignoring connection managers
    }
    elseif ($ParameterName.StartsWith("INTERN_","CurrentCultureIgnoreCase"))
    {
        # Internal parameters are ignored (where name starts with INTERN_)
        Write-Host "Ignoring Project parameter" $ParameterName " (internal use only)"
    }
    elseif ($Environment.Variables.Contains($Parameter.Name))
    {
        $ParameterCount = $ParameterCount + 1
        Write-Host "Project parameter" $ParameterName "connected to environment"
        $Project.Parameters[$Parameter.Name].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, $Parameter.Name)
        $Project.Alter()
    }
    else
    {
        # Variable with the name of the project parameter is not found in the environment
        # Throw an exeception or remove next line to ignore parameter
        Throw  [System.Exception]  "Project parameter $ParameterName doesn't exist in environment"
    }
}
Write-Host "Number of project parameters mapped:" $ParameterCount



#################################################
############## PACKAGE PARAMETERS ###############
#################################################
$ParameterCount = 0
# Loop through all packages
foreach ($Package in $Project.Packages)
{
    # Loop through all package parameters
    foreach ($Parameter in $Package.Parameters)
    {
        # Get parameter name and check if it exists in the environment
        $PackageName = $Package.Name
        $ParameterName = $Parameter.Name
        if ($ParameterName.StartsWith("CM.","CurrentCultureIgnoreCase"))
        {
            # Ignoring connection managers
        }
        elseif ($ParameterName.StartsWith("INTERN_","CurrentCultureIgnoreCase"))
        {
            # Internal parameters are ignored (where name starts with INTERN_)
            Write-Host "Ignoring Package parameter" $ParameterName " (internal use only)"
        }
        elseif ($Environment.Variables.Contains($Parameter.Name))
        {
            $ParameterCount = $ParameterCount + 1
            Write-Host "Package parameter" $ParameterName "from package" $PackageName "connected to environment"
            $Package.Parameters[$Parameter.Name].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, $Parameter.Name)
            $Package.Alter()
        }
        else
        {
            # Variable with the name of the package parameter is not found in the environment
            # Throw an exeception or remove next line to ignore parameter
            Throw  [System.Exception]  "Package parameter $ParameterName from package $PackageName doesn't exist in environment"
        }
    }
}
Write-Host "Number of package parameters mapped:" $ParameterCount



#################################################
##################### READY #####################
#################################################
# Kill connection to SSIS
$IntegrationServices = $null
 
 
Return "Ready deploying $IspacFileName "
The script

Thursday 14 December 2017

Azure Integration Services Preview (ADF V2)

Case
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.

Related Posts Plugin for WordPress, Blogger...