Azure-Enabled Integration Services Projects in Visual Studio


By:   |   Updated: 2020-04-24   |   Comments   |   Related: More > Azure

Problem

I’m developing SQL Server Integrations Services (SSIS) packages in Visual Studio. These packages are deployed into Azure using the Azure-SSIS Integration Runtime in Azure Data Factory (ADF). Sometimes packages fail when running in Azure while they run successfully in Visual Studio. For example, because a package tries to access a local database which is not possible from ADF. How can I test for such scenarios in Visual Studio, before I deploy to Azure?

Solution

Azure Data Factory (ADF) supports a lift-and-shift scenario for SSIS projects, where you can easily migrate your packages to Azure. In ADF, packages are executed using an Integration Runtime. You can find more information about this setup in the following tips:

However, development is still done using SQL Server Data Tools in Visual Studio on your local machine. This means there’s a big difference between the development environment (your on-premises machine on your on-premises network) and the execution environment (a cluster of virtual machines in Azure). Packages that work on your machine might not execute successfully after deployment. To deal with this scenario, Azure-Enabled SSIS projects are introduced. In such a project, you can choose to execute a package from Visual Studio on a linked Integration Runtime in Azure, instead of executing it in Visual Studio itself.

execute in Azure

This way, you can actually test if your package will run in Azure while you are still developing it in Visual Studio. Another advantage is you can execute very resource intensive packages – which might not run on your own machine because of limited resources – on a more powerful cluster in Azure.

The screenshots in this tip are taken using Visual Studio 2019 with the SQL Server Integration Services Projects extension (version 3.4).

Executing Packages in Azure from Visual Studio

Creating an Azure-Enabled Project

In Visual Studio, choose to create a new project. Search for “ssis”. With the latest extension installed, you’ll get two versions: the regular SSIS project and the Azure-Enabled SSIS project. Both are able to create the exact same projects and packages, but the Azure-Enabled project will allow you to debug in Azure.

create new project

Choose the Azure-Enabled project and give it a name and location in the next screen:

configure project

When the project is created, you can see in the solution explorer window the basic parts are still the same. However, a section Linked Azure Resources is added.

project created, linked Azure resources are added

Another difference with regular project is you can only set the TargetServerVersion to the latest version supported by the Azure-SSIS Integration Runtimes. At the time of writing, this is SQL Server 2017.

targetserverversion 2019 is not supported

This means if you want to develop packages for SQL Server 2019, you currently must use the regular SSIS projects.

Sample Package

To test if a package is really executing in Azure, we’re going to create a simple package that writes the name of the machine into a SQL Server table. First, create a new OLE DB connection manager:

create ole db conn mgr

In the next screen, choose New:

create new one

Fill in the name of the database server, configure the connection information and choose a database from the dropdown. As a database, I chose an Azure SQL DB so the package will work both in Visual Studio and in Azure.

configure conn mgr

In the database, we create a table with the following script:

CREATE TABLE dbo.AzureTest
(ID INT IDENTITY(1,1) NOT NULL
,ServerName VARCHAR(100) NOT NULL
,LoadDate DATETIME2 NOT NULL DEFAULT SYSDATETIME()
);

Back in the SSIS package, create a variable. The value is determined by the following expression:

"INSERT INTO dbo.[AzureTest]([ServerName])
VALUES ('" + @[System::MachineName]  + "');"

The MachineName variable is a system variable holding the name of the machine executing the SSIS package. This variable will change if we deploy it to another machine.

create variable

Add an Execute SQL Task to the control flow and configure it to use the variable and connection manager we just created:

configure execute sql task

When we execute the package in Visual Studio, we can see a row is added to the table using the local machine name:

local machine name written to table

Linking the Azure Resources

In the Solution Explorer, right-click on the Linked Azure Resources node. Click on Connect to SSIS in Azure Data Factory.

connect to ssis in adf

This will start a wizard to help you connect to the correct Azure resources.

ssis in adf wizard

In the next screen, choose your Azure subscription, the ADF environment and your Integration Runtime. You can switch between different accounts by clicking on your name in the top right corner.

choose azure resources

There are currently two types of Azure-SSIS IR, corresponding with the project and package deployment model: you have an IR with a SSIS catalog (as demonstrated in the tip Configure an Azure SQL Server Integration Services Integration Runtime), but you also have the newer type of IR where the packages are stored in Azure-Files. You can use either type of IR for your Azure-Enabled project. If you don’t have an IR yet, the following button will pop-up:

create new ir

Assuming an SSIS IR is present, we can continue to the next screen where we have to configure the Azure Storage connection.

configure Azure storage connection

In this storage account, SSIS will deploy and store the package and other artifacts needed to run the package in Azure.

When the wizard is done, you can see the configured items in Solution Explorer:

linked azure resources wizard done

If you right-click on any of the objects, you can choose manage and this will redirect you to the object in the Azure portal using your browser. If you want to change objects, for example another storage account, you can simply run the wizard again.

Executing the Package in Azure

To execute the package in the Azure-SSIS IR, right-click the package in Solution Explorer and choose Execute Package in Azure:

start from solution explorer

Alternately, you can click on the little arrow next to the “start debug” icon in the toolbar, and choose to execute the package in Azure from there:

start from debug arrow

When the package is started, it will be deployed into the Azure Storage account configured earlier. If the Azure-SSIS IR is not running, you will get a dialog asking to start it.

start azure-ssis ir

Keep in mind it can take an additionally 5 to 30 minutes to start the IR. If the IR is running, you can see the package executing in the progress window. However, there’s no visual execution like there is when running the package in Visual Studio itself.

package executed in azure

We can verify the package has actually run on another machine by checking the data in the test table:

machine name has changed

The progress window will also show you where the package and the logs are stored in the Storage account. If you browse to the folder ssdtexecution, you can see a folder being added with the project name and a timestamp:

folder added to storage account

Inside the folder, you’ll find an .ispac file containing the package and related artifacts, as well as the logs generated by the package:

artifacts generated in the storage account

Let’s suppose we now change the database connection to an on-premises database. If the SSIS IR is not connected to a Virtual Network or there’s no self-hosted IR to act as a gateway, the package cannot connect to the database and it will fail. When running in Visual Studio, the package will succeed:

package ok on-premises

However, when running the package in Azure it will fail. The error states it cannot make a connection to the on-premises database:

package fails in azure

When the development of the package has finished, don’t forget to stop your SSIS IR because they cost money to run.

Next Steps


Last Updated: 2020-04-24


get scripts

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips
Related Resources




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download


Recommended Reading

Adding Users to Azure SQL Databases

Connect to On-premises Data in Azure Data Factory with the Self-hosted Integration Runtime - Part 1

Continuous database deployments with Azure DevOps

Azure Data Factory Pipeline Email Notification Part 1

Transfer Files from SharePoint To Blob Storage with Azure Logic Apps





get free sql tips
agree to terms


Learn more about SQL Server tools