SSIS with Azure: A Comprehensive Guide

SSIS is a powerful tool for data integration, and integrating it with Azure can unlock a whole new level of scalability and flexibility.

Azure provides a managed environment for running SSIS packages, which means you can focus on developing your packages without worrying about the underlying infrastructure.

With Azure, you can create and manage SSIS projects in the cloud, and even integrate them with other Azure services like Azure Data Factory and Azure Data Lake Storage.

By leveraging Azure's scalable and secure environment, you can take your SSIS projects to the next level.

SSIS with Azure is a powerful combination that allows you to leverage your existing ETL (Extract, Transform, Load) skills and tools in the cloud. You can use SSIS with Azure to shift your workloads to the cloud and reduce costs.

If you're planning to use SSIS with Azure, you have two ETL approaches to choose from: Default Runtime (Copy Activity) and SSIS Runtime. The SSIS Runtime offers a more predictable pricing model, with a fixed monthly cost regardless of the number of executions.

You can also use SSIS with Azure if you like to use 3rd party SSIS components, such as SSIS PowerPack, or if you prefer the SSIS Package designer (SSDT) over ADF.

SSIS with Azure is a powerful combination that allows you to leverage your existing ETL skills in the cloud. You can use SSIS packages in Azure without significant changes, which is a major advantage.

One of the key benefits of using SSIS with Azure is the ability to use 3rd party SSIS components, such as SSIS PowerPack, which are not available in the Copy Activity. This gives you more flexibility and options for your ETL needs.

If you're already familiar with SSIS and want to shift your workloads to the cloud, SSIS with Azure is a great option. You can take advantage of a more predictable pricing model, with a fixed monthly cost regardless of the number of executions.

Here are some scenarios where SSIS with Azure might be a good fit:

In general, SSIS with Azure is a good choice if you want to use your existing ETL skills in the cloud, or if you prefer the more predictable pricing model of SSIS Runtime.

To get started with SSIS on Azure, you'll want to begin from the overview. To do this, select the Configure SSIS tile on the home page.

From here, you'll be guided through the process. For the remaining steps to set up an Azure-SSIS integration runtime, see the Provision an Azure-SSIS integration runtime section.

To set up SSIS with Azure, you'll need to create an Azure Data Factory instance. This is the first step in configuring your SSIS packages to run on Azure. You can do this by following the instructions in the "Getting Started – Setup SSIS Runtime in Azure" section.

You'll also need to download and install Azure Storage Explorer, which is a free tool that allows you to upload setup files and create SAS URLs for custom SSIS components. Additionally, you'll need to download and install SSMS v17.x or higher, as well as SSDT BI for VS 2015 or higher. These tools are required for designing and testing SSIS packages.

To deploy SSIS packages to Azure, you'll need to have one of the following versions of SQL Server Data Tools (SSDT): version 15.3 or later for Visual Studio 2017, or version 17.2 or later for Visual Studio 2015.

To set up SSIS with Azure, you'll need to create an Azure Data Factory instance. This can be done by following the steps outlined in the Azure portal. You'll need to select the correct subscription, resource group, and location for your instance.

You can choose the standard or enterprise edition for your integration runtime, depending on your needs. The standard edition is suitable for most use cases, while the enterprise edition provides additional features and support for advanced scenarios.

To configure your integration runtime, you'll need to select the location, node size, and number of nodes. You can choose from a range of node sizes and numbers, depending on your requirements. A larger node size and more nodes will provide more resources for your SSIS packages, but will also increase costs.

You'll also need to select the SQL Server edition for your integration runtime. This will determine the features and capabilities available to your SSIS packages. You can choose between the standard and enterprise editions, depending on your needs.

Before creating your integration runtime, you'll need to review the provisioning settings and bookmark the recommended documentation links. This will ensure that you have all the necessary information and settings for your integration runtime.

Here are the key settings to consider when creating your integration runtime:

Once you've created your integration runtime, you can start configuring it for your SSIS packages. This will involve selecting the correct settings and features for your packages, and configuring any additional components or custom setups as needed.

Azure supports regional setup for SSIS, with Data Factory and Azure-SSIS IR available in various regions.

For a seamless experience, it's essential to check the Data Factory and SSIS IR availability by region.

Setting up SSIS with Azure requires a solid understanding of the differences between SQL Database and SQL Managed Instance. SQL Database doesn't have the SQL Server Agent available, while SQL Managed Instance does.

You can create an SSISDB instance with a contained database user who represents any Microsoft Entra group with the managed identity of your data factory as a member in the db_owner role in SQL Database. In contrast, SQL Managed Instance allows you to create an SSISDB instance with a contained database user who represents the managed identity of your data factory.

The service tier for SSISDB can be selected when creating an Azure-SSIS IR with your Azure SQL Database server, but this isn't possible with SQL Managed Instance. All databases in a managed instance share the same resource allocated to that instance.

Here's a comparison of some key features between SQL Database and SQL Managed Instance:

Your Azure-SSIS IR can join an Azure Resource Manager virtual network if you use an Azure SQL Database server with IP firewall rules/virtual network service endpoints in SQL Database. With SQL Managed Instance, your Azure-SSIS IR can join an Azure Resource Manager virtual network if you use a managed instance with private endpoint.

Distributed transactions are supported through elastic transactions in SQL Database, but not supported in SQL Managed Instance.

Running SSIS with Azure can be a cost-effective solution. You can reduce costs by running SSIS on Azure Data Factory, which eliminates the need for a SQL Server license.

Azure Data Factory is a managed service, so you don't have to worry about hardware, OS, licensing, or ongoing maintenance/patches. This means you can focus on your ETL needs without the hassle of managing infrastructure.

To get started, create an Azure Data Factory instance and an Azure SQL DB instance to store the SSISDB. You'll also need to create a new SSIS Runtime in Azure Data Factory.

If you're using third-party SSIS components, create an Azure Blob Storage container and upload the setup files. Then, create a SAS URL for the container with the necessary permission.

Before deploying your SSIS packages, make sure you're using SSMS v17.x or higher, as older versions don't support ADF SSIS deployment.

Here are the steps to connect to the SSISDB catalog for ADF SSIS-IR:

1. Launch SSMS and click Connect -> Database Engine

2. Enter your Azure SQL Server Fully Qualified Domain Name, UserID, and Password

3. Go to Connection Properties Tab and enter SSIS Catalog DB as SSISDB

4. Click OK, and you'll see the Integration Services Catalog node in SSMS

You can also use the Execute SSIS Package Activity in Azure Data Factory to run your packages in a pipeline. This allows you to pass values to the package at runtime by creating SSIS execution environments and associating environment variables with project or package parameters.

To save costs on unused SSIS runtime hours, consider starting and stopping the SSIS Runtime just before and after executing the package. This technique can save you a significant amount of money if you're executing SSIS packages occasionally in ADF.

Moving your SSIS workloads to Azure can be a game-changer for your business. By doing so, you can reduce operational costs and the burden of managing infrastructure.

One of the main benefits of moving to Azure is that it can increase high availability. This is achieved through the ability to specify multiple nodes per cluster, as well as the high availability features of Azure and Azure SQL Database.

With Azure, you can also scale up your SSIS workloads by specifying multiple cores per node. This is a great option if you need to handle large amounts of data.

Another benefit of Azure is that it allows you to scale out your SSIS workloads by adding more nodes to your cluster. This is a great option if you need to handle a sudden surge in data processing.

Here's a comparison of SSIS on premises and SSIS on Azure:

This comparison highlights the key differences between SSIS on premises and SSIS on Azure.