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 |
