Running SSIS Integration Toolkit on the Cloud

30 April 2018
Daniel Cai

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-premise 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. 
  • 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 and run it with a prepared PowerShell script (provided below) in order to provision the Integration Runtime instance.

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 exact them before uploading).
    • One of the License Manager programs after the software installation.
    • Those token manager programs as needed if you are working with those OAuth 2.0 based connections (Azure Active Directory, Box, OneDrive, Google Services, HubSpot, Oracle Marketing Cloud, and QuickBooks).
  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 some token import scripts by running the token manager programs that we just discussed above. Azure-SSIS IR comes with one major restriction (supposedly for security reasons), you don't have a way to run interactive command, all initializations (such as the token import) have to be done at the time of the instance provisioning.
  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

We recommend you maintain a list of 4 PowerShell scripts.

  • 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 import a new token file for applicable connections (This is typically only needed once, as our software automatically manages the token lifecyle 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 = "" 

Login-AzureRmAccount
Select-AzureRmSubscription -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-AzureRmADServicePrincipal -ServicePrincipalName "MicrosoftAzureBatch").Id
    Register-AzureRmResourceProvider -ProviderNamespace Microsoft.Batch
    while(!(Get-AzureRmResourceProvider -ProviderNamespace "Microsoft.Batch").RegistrationState.Contains("Registered"))
    {
        Start-Sleep -s 10
    }
    # Assign VM contributor role to Microsoft.Batch
    New-AzureRmRoleAssignment -ObjectId $BatchObjectId -RoleDefinitionName "Classic Virtual Machine Contributor" -Scope $VnetId
}

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

$secpasswd = ConvertTo-SecureString $MySSISDBServerAdminPassword -AsPlainText -Force
$serverCreds = New-Object System.Management.Automation.PSCredential($MySSISDBServerAdminUserName, $secpasswd)
Set-AzureRmDataFactoryV2IntegrationRuntime  -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-AzureRmDataFactoryV2IntegrationRuntime -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."

Login-AzureRmAccount

Start-AzureRmDataFactoryV2IntegrationRuntime -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"

Login-AzureRmAccount

Stop-AzureRmDataFactoryV2IntegrationRuntime -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."

Login-AzureRmAccount

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

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

Create Your Own Blob Container

As we previously mentioned, you need your down container in order for the provisioning process to succeed. In order to create a blob container, you need to first create a storage account using your Azure subscription. This can be done easily within Azure Portal.

Adding an Azure Storage Account

Once you have an Azure Storage Account ready, you can then start Azure Storage Explorer and connect to your subscription, and you should be able to find your Storage Account in the explorer once you are connected. You would right click the "Blob Container" node underneath your Storage Account, and choose the "Create Blob Container" option. You would then specify a container name which is fairly straightforward.

Create a Blob Container

After the blob container has been created, you can right click the container, and choose the Get Shared Access Signature... option to get an SAS URL which you can use for the PowerShell script that we have been discussing.

Get Blob SAS URL

In the prompt window, you can configure how the SAS grant works, it is important to note that you need to have the Write privilege enabled if you want the Azure-SSIS IR provisioning process write a log file back to your container (which is the way that we have done in the main.cmd script).

Configure Blob SAS Options

Once you have clicked the Create button, you will get a URL that you can use in your PowerShell script.

Copy Azure Blob SAS URL

KingswaySoft Shared Blob Container

The following is the URL you can use to get a list of all of our recent installation programs along with a License Manager program and all the Token Manager programs. The container also provides a sample main.cmd file that you can modify for your use. You can attach to our container so that you can copy or download files from our container to your own container where you can make modifications to main.cmd script to include license activation with your own key.

https://ssistoolkit.blob.core.windows.net/ultimate-edition?st=2018-04-30T17%3A48%3A03Z&se=2050-05-01T17%3A48%3A00Z&sp=rl&sv=2017-04-17&sr=c&sig=AJiJ8IbxFfxjFu9hAEkKLK2SSUeS2GyyZ07Umrbz04o%3D

A few things to note when using our blob container.

  • The container will only have our most recent releases. If you ever need to run a particular version in the future, you will have to manually extract our installation packages and upload to your container yourself - if you ever do so, please make sure that you are using our Spring 2018 release or later. Prior versions will not work properly from licensing perspective.
  • We don't guarantee the availability of this particular container.
    • In case the above URL does not work anymore, you can always extract the installation files you can get from our download pages (do please always get the most recent release - at least the Spring 2018 release or later).
    • In case we have changed to a new one, we will post the new URL here, you are welcome to check this blog post.
  • You should NOT use our container directly in your PowerShell provisioning script, since our container is shared in a read-only mode, you have no way to make any changes to the main.cmd which contains a license activation command that will actually fail because of a fake key used. In addition, if you are ever interested in reading the provisioning log, you can't do that with our container, as it is a read-only access.
  • To connect (or attach) to our blob container, you would first right click on the "Storage Accounts" node under "(Local and Attached)", and choose "Connect to Azure storage..." as shown below.

    Connect to KingswaySoft Shared Blob Container

    In the next page, you would choose the option that to enter an SAS URL as shown below.

    Connect to KingswaySoft Shared Blob Container - Step 2

    In the last step, you would enter the URL that we have shared above.

    Connect to KingswaySoft Shared Blob Container - Step 3

    Once this is done, you should have connected (or attached) to our container from where you can download our latest and greatest SSIS Integration Toolkit software. Within the container, you can see all the installation files from our SSIS Integration Toolkit product family. You may not necessarily need them all. You can remove any of them as needed - do note you need to update main.cmd file if you have removed any of them when copying/downloading from our container.

    What is in KingswaySoft Shared Blob Container

The main.cmd File

The following is the file content of the main.cmd file in our blob container in case the container is not available.

::Use following commands as needed to install KingswaySoft SSIS Integration Toolkit software on Azure Data Factory v2

@echo off
echo Activating a license, make sure the License Key, email address and Licensee information are all updated below.
KingswaySoft.LicenseManager.exe /a /k XXXXX-XXXXX-XXXXX-XXXXX-XXXXX-XXXXX /e [email protected] /l "Company Name"
echo License activation completed.

::echo Import HubSpot token file (you can use similar command syntax to import another connection manager's OAuth token)
::KingswaySoft.HubSpot.TokenManager.exe /i /p tokens.json /o

echo Start to IntegrationToolkit-Dynamics365-x64.msi
msiexec /i IntegrationToolkit-Dynamics365-x64.msi /qn /lv %CUSTOM_SETUP_SCRIPT_LOG_DIR%\install.log ACCEPT_EULA=1
echo Finished installation of IntegrationToolkit-Dynamics365-x64.msi

echo Start to IntegrationToolkit-ProductivityPack-x64.msi
msiexec /i IntegrationToolkit-ProductivityPack-x64.msi /qn /lv %CUSTOM_SETUP_SCRIPT_LOG_DIR%\install.log ACCEPT_EULA=1
echo Finished installation of IntegrationToolkit-ProductivityPack-x64.msi

echo Start to IntegrationToolkit-SharePoint-x64.msi
msiexec /i IntegrationToolkit-SharePoint-x64.msi /qn /lv %CUSTOM_SETUP_SCRIPT_LOG_DIR%\install.log ACCEPT_EULA=1
echo Finished installation of IntegrationToolkit-SharePoint-x64.msi

echo Start to IntegrationToolkit-Salesforce-x64.msi
msiexec /i IntegrationToolkit-Salesforce-x64.msi /qn /lv %CUSTOM_SETUP_SCRIPT_LOG_DIR%\install.log ACCEPT_EULA=1
echo Finished installation of IntegrationToolkit-Salesforce-x64.msi

echo Start to IntegrationToolkit-OracleCRMOnDemand-x64.msi
msiexec /i IntegrationToolkit-OracleCRMOnDemand-x64.msi /qn /lv %CUSTOM_SETUP_SCRIPT_LOG_DIR%\install.log ACCEPT_EULA=1
echo Finished installation of IntegrationToolkit-OracleCRMOnDemand-x64.msi

echo Start to IntegrationToolkit-Marketo-x64.msi
msiexec /i IntegrationToolkit-Marketo-x64.msi /qn /lv %CUSTOM_SETUP_SCRIPT_LOG_DIR%\install.log ACCEPT_EULA=1
echo Finished installation of IntegrationToolkit-Marketo-x64.msi

echo Start to IntegrationToolkit-Magento-x64.msi
msiexec /i IntegrationToolkit-Magento-x64.msi /qn /lv %CUSTOM_SETUP_SCRIPT_LOG_DIR%\install.log ACCEPT_EULA=1
echo Finished installation of IntegrationToolkit-Magento-x64.msi

echo Start to IntegrationToolkit-DynamicsSL-x64.msi
msiexec /i IntegrationToolkit-DynamicsSL-x64.msi /qn /lv %CUSTOM_SETUP_SCRIPT_LOG_DIR%\install.log ACCEPT_EULA=1
echo Finished installation of IntegrationToolkit-DynamicsSL-x64.msi

echo Start to IntegrationToolkit-DynamicsGP-x64.msi
msiexec /i IntegrationToolkit-DynamicsGP-x64.msi /qn /lv %CUSTOM_SETUP_SCRIPT_LOG_DIR%\install.log ACCEPT_EULA=1
echo Finished installation of IntegrationToolkit-DynamicsGP-x64.msi

echo Start to IntegrationToolkit-NetSuite-x64.msi
msiexec /i IntegrationToolkit-NetSuite-x64.msi /qn /lv %CUSTOM_SETUP_SCRIPT_LOG_DIR%\install.log ACCEPT_EULA=1
echo Finished installation of IntegrationToolkit-NetSuite-x64.msi

echo Start to IntegrationToolkit-OracleMarketingCloud-x64.msi
msiexec /i IntegrationToolkit-OracleMarketingCloud-x64.msi /qn /lv %CUSTOM_SETUP_SCRIPT_LOG_DIR%\install.log ACCEPT_EULA=1
echo Finished installation of IntegrationToolkit-OracleMarketingCloud-x64.msi

echo Start to IntegrationToolkit-Xero-x64.msi
msiexec /i IntegrationToolkit-Xero-x64.msi /qn /lv %CUSTOM_SETUP_SCRIPT_LOG_DIR%\install.log ACCEPT_EULA=1
echo Finished installation of IntegrationToolkit-Xero-x64.msi

echo Start to IntegrationToolkit-QuickBooks-x64.msi
msiexec /i IntegrationToolkit-QuickBooks-x64.msi /qn /lv %CUSTOM_SETUP_SCRIPT_LOG_DIR%\install.log ACCEPT_EULA=1
echo Finished installation of IntegrationToolkit-QuickBooks-x64.msi

echo Start to IntegrationToolkit-HubSpot-x64.msi
msiexec /i IntegrationToolkit-HubSpot-x64.msi /qn /lv %CUSTOM_SETUP_SCRIPT_LOG_DIR%\install.log ACCEPT_EULA=1
echo Finished installation of IntegrationToolkit-HubSpot-x64.msi

echo Start to IntegrationToolkit-DirectoryServices-x64.msi
msiexec /i IntegrationToolkit-DirectoryServices-x64.msi /qn /lv %CUSTOM_SETUP_SCRIPT_LOG_DIR%\install.log ACCEPT_EULA=1
echo Finished installation of IntegrationToolkit-DirectoryServices-x64.msi

echo Start to IntegrationToolkit-TeamFoundationServer-x64.msi
msiexec /i IntegrationToolkit-TeamFoundationServer-x64.msi /qn /lv %CUSTOM_SETUP_SCRIPT_LOG_DIR%\install.log ACCEPT_EULA=1
echo Finished installation of IntegrationToolkit-TeamFoundationServer-x64.msi

echo Start to IntegrationToolkit-ProjectServer-x64.msi
msiexec /i IntegrationToolkit-ProjectServer-x64.msi /qn /lv %CUSTOM_SETUP_SCRIPT_LOG_DIR%\install.log ACCEPT_EULA=1
echo Finished installation of IntegrationToolkit-ProjectServer-x64.msi

Development, Deployment and Scheduling

Development will be exactly the same as you develop for on-premise SSIS installations. You would want to use the latest SSDT installation that you can download from Microsoft website. At the time of deployment, instead of targeting an on-premise SQL installation, you would target the server that hosts your SSISDB database on cloud using an Azure SQL login. The entire deployment process is identical to what you do with an on-premise installation except that you would target the server on the cloud using an SQL login as we just mentioned (integrated authentication is not an option in this case).

For scheduling, there are three options, as described below with respective links provided:

A few final notes

It is worth noting the following restrictions or tips when working with Azure-SSIS Integration Runtime due to the special deployment infrastructure:

  • We only provide subscription-based license for your Azure-SSIS Integration Runtime deployment.
  • We only offer the Ultimate license due to the inconvenience of making any license changes after an instance is provisioned.
  • Our software does automatically request a trial license after it is initially provisioned, if a license does not exist in the system.
  • You need to use our most recent Spring 2018 release or later in order to work with the Azure-SSIS Integration Runtime.
  • The installation packages shared in our blob container do not include the integration toolkit for Parature and Dynamics Marketing (this is different from Dynamics 365 for Marketing), as both of them are deprecated.
  • When provisioning your Azure-SSIS instance to work with Microsoft Dynamics 365 online, you want to pick a location that is as close as possible to your Dynamics 365 online server so that you can get the best possible performance due to a low network latency which you can almost never get with an on-premise SSIS installation.

Happy cloud-SSIS'ing!

https://azure.microsoft.com/en-us/features/storage-explorer/
comments powered by Disqus