Running SSIS Integration Toolkit on the Cloud (Legacy Approach)

Note: This is the original writing of the newly updated Running SSIS Integration Toolkit on the Cloud blog post (with some minor editing). We keep this for reference purposes in case it might be still useful. In general, you should be using the self-served or express custom setup discussed in the new blog post, which is a lot more easier.

With all the recent trends of moving to the cloud in the industry, we have received a lot of interests from our clients about running our Integration Toolkit software on the cloud. Traditionally this is only possible through running our software in a fully configured virtual machine on the cloud. This is less than ideal, since you still have to worry about Windows operating system configuration, SQL Server installation, security patches and everything else. You get some benefits of cloud computing, but not at the full scale. The good news is, Microsoft has recently made the innovative move to make SSIS available as part of the Azure Data Factory (ADF) v2 offering. We are happy to announce that our software is now fully embracing the power of ADF v2, you can now run our software completely on the cloud using ADF v2 SSIS Integration Runtime after our Spring 2018 release that we made available on April 26, 2018.

Why is this such a big deal?

Without going to the great details of the general overall benefits of cloud computing, here are some special benefits of running SSIS on the cloud in the way we interpret this move.

• SSIS has been mostly considered an on-premises only solution, which carries a legacy tag whenever a discussion is brought up about modern cloud integration. With its availability on ADF v2, SSIS becomes a first-class integration offering that brings all its power and flexibility to the cloud.
• Running integration software on cloud means that you have the closest proximity to the application server that you might integrate with. Giving an example, if your integration involves writing data to Microsoft Dynamics 365, you want your integration software to be as close as possible to the Dynamics 365 server as the network latency plays a significant role in affecting your integration performance. With the ability to pick whichever data center that your Azure-SSIS Integration Runtime (IR) resides, you have the opportunity to pick an instance that has the lowest possible network latency to your Dynamics 365 online server which offers the best possible reading and writing performance.
• Of course, all other cloud computing benefits apply as well, for instance, you don't have to worry about operating system configuration, SQL Server installation, licensing, security patches, also you can easily scale your instance up or down as your integration needs change.

Prerequisites

In order to run our software on the cloud, you will need to meet the following prerequisites.

• You need to have an Azure SQL subscription that can host your SSISDB which is required when provisioning your Azure-SSIS Integration Runtime instance. The Azure SQL instance does not have to be blank, it can be even an existing database subscription, the only requirement is it cannot have an SSISDB database, as that will be exclusively used by the Azure-SSIS IR engine. 
  • SSISDB is no longer a requirement as a result of some recent platform enhancements - but this blog post still provisions an SSISDB database as it still provides some of the benefits without it - you may however tweak the script to not provision the database.
• You need an Azure Blob Storage account and you need to use Azure Storage Explorer to upload the installation package of our software (the .msi files) along with the necessary command line file to the container (provided in our blob container which you can link to in the Azure Storage Explorer - you can then download or copy to your own container).
• You need to download and install one of the recent Azure PowerShell releases (You should be downloading one of the recent Az releases) and run it with a prepared PowerShell script (provided below) in order to provision the Integration Runtime instance.
  • After installing the PowerShell release, you would typically start by running the following command line in order to import the necessary modules.
    

    Install-Module -Name Az -Repository PSGallery -Force -SkipPublisherCheck
    Import-Module -Name Az

How does it work?

The entire process involves a few steps below. The following are the brief descriptions of the entire procedure.

1. First, you need to ensure you have an Azure SQL Server instance if you don't have one yet. This is easy, you can add one in your Azure Portal. You want to make a note of the database server name that you have just created, also the admin account and its password as well, you will need such information in the PowerShell script when provisioning the Azure-SSIS Integration Runtime.
2. You will need to upload the following files to your blob container (create one if you don't yet have) using the Azure Storage Explorer. Note that you can get the whole list of files from our shared blob container that we will discuss below.
   
   • Our software installation files which you can download from our website (make sure you extract them before uploading).
   • One of the License Manager programs after the software installation.
3. You will then need to prepare a " main.cmd" file that contains your custom setup script to be run at the time when your Azure-SSIS IR is provisioned. Your script may contain the following kinds of commands (we have included below a template file that you can use in our shared blob container).
   • Mainly you need to provide msiexec commands that run our software installation programs (shipped in .msi format) on the server.
   • You can include a license activation command line to activate a license key that you have acquired from us. In the case you don't have such a key available yet, you can leave the license activation command commented out as the way it is in the sample script file, our software will try to get a 14-day trial license automatically upon its first execution.
   • Additionally, you may include OAuth token files that are needed for SSIS runtime. In such cases, you would save the token files in the blob container, and add a line of code in the command file (main.cmd) to copy it to the IR. In doing so, you need to make sure that your connection manager is using the path where the token file resides. You can safely assume there is the C drive on the IR. In the copy command, you can use . as the current folder of the blob container. We recommend you use the xcopy command when making the file copy in case the file needs to reside under a subfolder of the C drive.
4. You need to prepare a PowerShell script (a sample is provided below), and run it to provision and start your Azure-SSIS IR instance. In the script, you need to provide a SAS URL that is generated from the Blob Container you have created in step #2. When your Azure-SSIS IR involves a third-party or in-house developed components, you can't provision it from Azure Portal, you will need to run it through PowerShell scripts, as Azure Portal UI does not have a way to specify the SAS URL for your blob container.

That's really all involved. If you have got everything right, it will take about 20-30 minutes to have the instance provisioned.

PowerShell Scripts [Updated - Jan 20, 2020]

We recommend you maintain a list of 4 PowerShell scripts.
[Update Note - Jan 20, 2020 - We have updated the scripts to use the more recent Az PowerShell module instead of the deprecated AzureRm module previously]

• An Initialization script that provisions your Azure-SSIS Integration Runtime
• An Update and Start script that can be used to make changes or updates to your Azure-SSIS Integration Runtime for one (or more) of the following reasons
  • You want to update our software installation to a different version
  • You need to re-license our software by changing to a different key or apply a maintenance, or anything in that nature
  • You need to copy a new token file for applicable connections (This is typically only needed once, as our software automatically manages the token lifecycle once the initial import is done)
• A Start script to start the integration runtime instance
• A Stop script to stop your integration runtime instance

The last two are optional, as the same can be achieved within Azure Portal UI. The following are the scripts:

Initializations.ps1

#Initializations.ps1, this is based upon https://docs.microsoft.com/en-us/azure/data-factory/create-azure-ssis-integration-runtime#azure-powershell

# Azure Data Factory version 2 information
$MySubscriptionName = "My Azure Subscription Name"
$MyResourceGroupName = "My Azure Resource Group Name"
$MyDataFactoryName = "My Azure Data Factory Name"
$MyDataFactoryLocation = "My Azure Data Factory Location" # An example would be EastUS

# Azure-SSIS integration runtime information - This is the Data Factory compute resource for running SSIS packages
$MyAzureSsisIrName = "My Azure-SSIS Integration Runtime name"
$MyAzureSsisIrDescription = "Azure-SSIS integration runtime with KingswaySoft products installed"
$MyAzureSsisIrLocation = "My Azure-SSIS Integration Runtime Location" # An example would be EastUS
$MyAzureSsisIrNodeSize = "My Azure-SSIS Integration Runtime Node Size" # Available options are Standard_A4_v2|Standard_A8_v2|Standard_D1_v2|Standard_D2_v2|Standard_D3_v2|Standard_D4_v2
$MyAzureSsisIrNodeNumber = 1
$MyAzureSsisIrMaxParallelExecutionsPerNode = 2 # 1-8 parallel executions per node are supported

# SSISDB info
$MySSISDBServerEndpoint = "The Azure SQL Server that will host the SSISDB database for the runtime"
$MySSISDBServerAdminUserName = "The admin user for the above Azure SQL Server"
$MySSISDBServerAdminPassword = "The admin user's password for the above Azure SQL Server"

# Setup Script Container
$MySetupScriptContainerSasUri = "The URL to the blob container where the KingswaySoft installation programs reside along with main.cmd script, the License Manager program, and Token Manager programs, etc."

# Remove the SSISDBPricingTier variable if you are using Azure SQL Managed Instance (private preview)
$SSISDBPricingTier = "Basic" # Applies only to Azure SQL Database. For the basic pricing tier, specify "Basic", not "B". For standard tiers, specify "S0", "S1", "S2", 'S3", etc.

## Remove these two OPTIONAL variables if you are using Azure SQL Database. 
## These two parameters apply if you are using VNet and Azure SQL Managed Instance (private preview). 
# In public preview, only classic virtual network (VNet) is supported.
$VnetId = "" 
$SubnetName = "" 

Connect-AzAccount
Select-AzSubscription -SubscriptionName $MySubscriptionName

$SSISDBConnectionString = "Data Source=" + $MySSISDBServerEndpoint + ";User ID="+ $MySSISDBServerAdminUserName +";Password="+ $MySSISDBServerAdminPassword
$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;
    }
}

# 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
}

Set-AzDataFactoryV2 -ResourceGroupName $MyResourceGroupName `
                    -Location $MyDataFactoryLocation `
                    -Name $MyDataFactoryName

$secpasswd = ConvertTo-SecureString $MySSISDBServerAdminPassword -AsPlainText -Force
$serverCreds = New-Object System.Management.Automation.PSCredential($MySSISDBServerAdminUserName, $secpasswd)
Set-AzDataFactoryV2IntegrationRuntime  -ResourceGroupName $MyResourceGroupName `
                                       -DataFactoryName $MyDataFactoryName `
                                       -Name $MyAzureSsisIrName `
                                       -Type Managed `
                                       -CatalogServerEndpoint $MySSISDBServerEndpoint `
                                       -CatalogAdminCredential $serverCreds `
                                       -CatalogPricingTier $SSISDBPricingTier `
                                       -Description $MyAzureSsisIrDescription `
                                       -Location $MyAzureSsisIrLocation `
                                       -NodeSize $MyAzureSsisIrNodeSize `
                                       -NodeCount $MyAzureSsisIrNodeNumber `
                                       -MaxParallelExecutionsPerNode $MyAzureSsisIrMaxParallelExecutionsPerNode `
                                       -SetupScriptContainerSasUri $MySetupScriptContainerSasUri

write-host("##### Starting your Azure-SSIS Integration Runtime, this may take 20 to 30 minutes to complete #####")
Start-AzDataFactoryV2IntegrationRuntime -ResourceGroupName $MyResourceGroupName `
                                        -DataFactoryName $MyDataFactoryName `
                                        -Name $MyAzureSsisIrName `
                                        -Force

write-host("##### Completed #####")
write-host("If any cmdlet is unsuccessful, please consider using -Debug option for diagnostics.")

Start.ps1

#Start.ps1

$MyResourceGroupName = "My Azure Resource Group Name"
$MyDataFactoryName = "My Azure Data Factory Name"
$MyAzureSsisIrName = "My Azure-SSIS Integration Runtime name"
$MySetupScriptContainerSasUri = "The URL to the blob container where the KingswaySoft installation programs reside along with main.cmd script, the License Manager program, and Token Manager programs, etc."

Connect-AzAccount

Start-AzDataFactoryV2IntegrationRuntime -DataFactoryName $MyDataFactoryName `
                                        -Name $MyAzureSsisIrName `
                                        -ResourceGroupName $MyResourceGroupName

Stop.ps1

# Stop.ps1

$MyResourceGroupName = "My Azure Resource Group Name"
$MyDataFactoryName = "My Azure Data Factory Name"
$MyAzureSsisIrName = "My Azure-SSIS Integration Runtime name"

Connect-AzAccount

Stop-AzDataFactoryV2IntegrationRuntime -DataFactoryName $MyDataFactoryName `
                                       -Name $MyAzureSsisIrName `
                                       -ResourceGroupName $MyResourceGroupName

UpdateAndStart.ps1

# UpdateAndStart.ps1

$MyResourceGroupName = "My Azure Resource Group Name"
$MyDataFactoryName = "My Azure Data Factory Name"
$MyAzureSsisIrName = "My Azure-SSIS Integration Runtime name"
$MySetupScriptContainerSasUri = "The URL to the blob container where the KingswaySoft installation programs reside along with main.cmd script, the License Manager program, and Token Manager programs, etc."

Connect-AzAccount

Set-AzDataFactoryV2IntegrationRuntime -DataFactoryName $MyDataFactoryName `
                                      -Name $MyAzureSsisIrName `
                                      -ResourceGroupName $MyResourceGroupName `
                                      -SetupScriptContainerSasUri $MySetupScriptContainerSasUri

Start-AzDataFactoryV2IntegrationRuntime -DataFactoryName $MyDataFactoryName `
                                        -Name $MyAzureSsisIrName `
                                        -ResourceGroupName $MyResourceGroupName

Create Your Own Blob Container

For the process of creating your own blob container, please refer to the same section in the updated blog post.

KingswaySoft Shared Blob Container

For the use of KingswaySoft shared blob container, please refer to the same section in the updated blog post.

The main.cmd File

For the content of the main.cmd file, please refer to the same section in the updated blog post.