Introduction

 

**UPDATE 2018/07/31**
Xtract IS for Azure has been released to public. Please check our online help on where to download and how to set up the software. If you have questions, please contact us at support@theobald-software.com.

**UPDATE 2018/04/20**
Azure-SSIS IR has moved to public preview. Microsoft has provided this guideline on how to set up custom components in Azure-SSIS IR.  It’s basically the same steps we followed in our test runs. Feel free to contact us for a public preview version of Xtract IS for Azure. Please send us an email at support@theobald-software.com.

We recently had the chance to test run a private preview version of the Integration Runtime (IR) in Azure Data Factory v2 (ADFv2).

With Azure-SSIS, the IR offers SSIS capability through a native compute resource in ADFv2 by providing a managed cluster of Azure VMs (nodes). The idea is to deploy, configure and run your locally developed SSIS packages in the Azure cloud just like you would with SSIS on premises.

While Azure-SSIS has been available for public preview since September 2017, running custom SSIS or 3rd party SSIS components currently is not. 

With its product Xtract IS, Theobald Software has provided SAP connectivity to SSIS for more than a decade. This private preview gave us a chance to test run and adapt Xtract IS in a way so that SSIS packages containing Xtract IS components would run in Azure-SSIS.

The goal of this blog post is to provide some first hand information on how we set up the required environment to make Xtract IS available in Azure-SSIS.

 

Prerequisites and Setup

Before we could start the evaluation, a few preliminary steps needed to be taken.

  • First of all we needed to set up an ADFv2 infrastructure that allowed us to deploy and run SSIS packages in Azure. This feature is already available in the public preview version of ADFv2. For the setup we followed this tutorial.
  • For connecting Azure to on premises data sources, in this case our local SAP installation, we set up a Point-to-site VNet.

The following steps were specific to the private preview:

  • Our Azure subscription needed to be whitelisted by Microsoft for this private preview.
  • A private preview version of Azure PowerShell ISE as well as a short tutorial were provided by Microsoft.

For the installation of Xtract IS, we needed to create an Azure storage container in which we placed a slightly customized Xtract IS setup file. The container is linked to the IR via a Shared Access Signature. On startup of the IR, the Xtract IS setup will be automatically executed.

For configuring and managing the IR, we used a number of PowerShell commandlets. In the meantime, visual tools for ADFv2 have been made available for public preview.

Azure-SSIS_PowerShell

For managing the Blob container, we used the desktop client tool Azure Storage Explorer.

Azure-SSIS_StorageExplorer

All in all, the following Azure resources were created:

Azure_all_Azure_Resources

 

SSIS package development

Two simple SSIS package were created. Each of them extracted data from an SAP table and directly wrote that data to a SQL Server database hosted on Azure.

One package connected to an SAP system in the cloud, the other package connected to our on premises SAP system (for which we created the VNet, see above).

We found there is no difference in package development whether deploying and running the packages on premises or in Azure-SSIS.

SSIS Packages were created using SSDT2015 for Target Server Version SQL Server 2016.

SSIS package

 

SSIS package deployment and execution

The SSIS packages were deployed to an Azure SQL Server SSISDB catalog that was automatically created during the initial setup of the IR in Azure-SSIS (see Prerequisites and Setup).

Once deployed, the packages in the SSISDB catalog were manually executed from SQL Server Management Studio.

SSIS package execution

 

Summary and outlook

This blog outlined the steps we took to make Xtract IS available in a private preview version of Azure-SSIS. We succeeded in installing Xtract IS in Azure-SSIS as well as deploying and running SSIS packages containing Xtract IS components in that environment. All in all we found the whole process straight forward and did not encounter any major challenges.

Xtract IS is ready for Azure-SSIS and we are looking forward to the official release date of ADFv2.

Feel free to contact us at info@theobald-software.com in case of questions.