Migrate a Package Deployment Integration Services Project to Azure


By:   |   Updated: 2020-05-06   |   Comments (1)   |   Related: More > Azure Data Factory


Problem

You can migrate existing SQL Server Integration Service (SSIS) projects to an Azure Data Factory environment and using an Integration Runtime, you can run your SSIS package in a managed service. However, what if your project is using the package deployment model? Is it still possible to lift-and-shift your project to Azure?

Solution

Azure Data Factory (ADF) allows you to migrate existing SSIS projects to a managed service. Using Integration Runtimes, packages can be executed in a cluster of virtual machines, all managed by ADF. The following tips explain how you can set up such an environment:

However, the Azure-SSIS IR uses a catalog stored in either an Azure SQL DB or a SQL Server Managed Instance. This means the SSIS projects needs to use the project deployment model (see the tips SSIS Project Deployment Model in SQL Server 2012 (Part 1 of 2) and Integration Services (SSIS) Convert from Package Deployment Model to Project Deployment Model for more information about the package and project deployment model in SSIS).

But what if your on-premises SSIS project still uses the package deployment model? Do you need to convert it to the project deployment model first? Although that is an option, you can now also create a new type of Integration Runtime; instead of using a SSIS catalog, you’ll use Azure Blob Storage to store your SSIS packages.

Azure-SSIS IR with Azure Files

Let’s start with creating a new Integration Runtime (IR). In Azure Data Factory, click on Configure SSIS Integration.

start ir config

This will open up the configuration of a new IR. Specify a name, location, the node size of the virtual machines, the number of nodes and the edition.

ir config first screen

There are some tricks to save money, because you will be paying for every second the cluster of virtual machines is running:

  • If you don’t use Enterprise SSIS features, keep the edition on Standard. There are only a few components that need Enterprise edition.
  • If you already have a SQL Server license (for example from MSDN, partnership, volume licensing etc.), you can choose to use the Azure Hybrid Benefit, which severely reduces the cost of the IR.
  • Think wisely about the number of nodes you actually need and the size of the node. If you have an ETL flow which focuses on executing SQL in a SQL Server database, you can choose a very small node size since most of the computational work is done by the database.
  • Only start the IR when you actually need it and shut it down after the packages have done running. You can find an example in the tip Automate the Azure-SSIS Integration Runtime Start-up and Shutdown - Part 1,  but recently the ADF team has published APIs that can do this for you as well. More information can be found in the documentation.

On the next screen, the following default is presented:

default ssis catalog

As mentioned before, this default uses a SSIS catalog to store the SSIS projects and packages. If you do not want this and you rather want to use the package deployment method, simply deselect the checkbox:

create azure-files IR

Hovering over the question mark icon with your mouse displays the following message:

explanation of checkbox

On the next package, you can configure some additional settings like the number of parallel executions:

additional settings

As the final step, you can review the configuration of the IR you’re about to deploy:

integration runtime setup

And that’s it. Your Azure-SSIS IR is now created.

Sample Project and Package

The test package holds a single Execute SQL Task. There are two variables created:

  • TestMessage - This value has a default value "Hello World!", but it will be overwritten by a package configuration.
  • SQLStatement - This variable uses an expression to compose a SQL INSERT statement that will insert the value of the TestMessage variable into a table.
variables definition

The Execute SQL Task executes the SQLStatement variable in an Azure SQL Database.

execute sql task

The table dbo.PackageConfigTest can be created with the following DDL statement:

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

Finally, a package configuration has been created that will update the value of the TestMessage variable using an XML config file:

package config

The XML config file has the following contents:

<?xml version="1.0"?>
<DTSConfiguration>
  <DTSConfigurationHeading>
    <DTSConfigurationFileInfo GeneratedBy="mydomain\myuser" GeneratedFromPackageName="Package" GeneratedFromPackageID="{47735934-A4BA-4AA4-A372-4D2227D39C24}" GeneratedDate="3/23/2020 9:57:28 AM"/>
  </DTSConfigurationHeading>
  <Configuration ConfiguredType="Property" Path="\Package.Variables[User::TestMessage].Properties[Value]" ValueType="String">
    <ConfiguredValue>Hello MSSQLTips!</ConfiguredValue>
  </Configuration>
</DTSConfiguration>
			

The package itself is protected with the protection level EncryptSensitiveWithPassword, to secure the connection string to the Azure SQL DB.

When the package is run with the config disabled, the following data is inserted into the test table:

test without config

With the package config enabled, we get the following result:

test with config

You can download the package and the config file here (the password is mssqltips). Make sure to add it to a project configured to use the package deployment model, since it is not the default:

convert to package deployment

Deploying the Package to Azure

This step is fairly straight forward. You just copy the package and the config file to an Azure File Share. You can use the free tool Azure Storage Explorer.

azure file share

You can find more information about Azure File Shares (formerly called Azure File Services) in the tip Using Files Stored in Azure File Services with Integration Services - Part 1.

Execute a Package in Azure Data Factory

To test the integration runtime, we’re going to run the test package in an Azure Data Factory pipeline. In a pipeline, add the Execute SSIS package activity. In the settings pane, choose the IR we created earlier and select the file system as the location for the package.

configure package location

Browse to the package location and the location of the config file. You can use the browse file storage button to use a user interface to browse to the files.

configure security

Next, configure the access credentials needed to access the Azure Storage location (in my case it was already pre-filled) and specify the password for the protection level (mssqltips). You can also set up a location for the log files. The pipeline can now be executed:

execute ssis package

We can see a new row was added to the test table, using the XML config file to change the message:

final results

In the log folder, we can find the following information:

log results

Surprisingly, it is not one big log file like we’re used to in the package deployment model (more info in this tip), but rather a structure that reminds us of the logging used in the SSIS catalog.

Next Steps


Last Updated: 2020-05-06


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





Comments For This Article




Wednesday, May 06, 2020 - 12:44:45 PM - Anne Cao Back To Top (85584)

Thanks for the tips. We are using a lot of package deployed model for SSIS projects. This really helps!



download





Recommended Reading

Azure Data Factory Pipeline Email Notification Part 1

Azure Data Factory Lookup Activity Example

Azure Data Factory Get Metadata Example

Azure Data Factory vs SSIS vs Azure Databricks

Getting Started with Delta Lake Using Azure Data Factory








get free sql tips
agree to terms


Learn more about SQL Server tools