How to use Azure-SSIS integration runtime to run SSIS packages in Azure and Azure Government

Aarti Riley

June 29th, 20223 0

Integration runtime for Azure-SQL Server Integration Services (SSIS) makes it possible to simply run SSIS packages in Azure and Azure Government. The feature provides similar functionality and behavior to when you schedule SSIS packages by using SQL Server Agent in your on-prem environment. Azure Data Factory provides the engine to execute packages instead of SQL Server Integration Services.

With this feature, you can run SSIS packages that are stored in SSISDB in a SQL Managed Instance, or a file system like Azure Files. The two additional components that are required for this feature along with Azure SQL Managed Instance or an Azure SQL database, and SSMS are Azure Data Factory and Integration Runtime for Azure SSIS.

  1. Create Azure Data Factory (ADF).
  1. Create Integration Runtime (IR) for Azure-SSIS.
    • Documentation on provisioning the Azure-SSIS IR in ADF.
    • Specify where your packages will be hosted.
      • Click “Create SSIS catalog (SSISDB) hosted by Azure SQL…” to host your packages in SSISDB on an Azure SQL Db or an Azure SQL Managed Instance. This option will create SSISDB in the Azure SQL MI.
      • Click “Create package store to manage your packages to host your packages into Azure files, or MSDB. You will need a file share created in a storage account.
    • Once created, the IR will take a few minutes to start.
    • The location (region) of your Azure-SSIS IR should be the same as the location (region) of the SSIDB on Azure SQL DB or Azure SQL Managed Instance to minimize cross-location traffic.
    • The following table shows the options available to you depending on where you want to host your packages.

Image SSIS Image 10 8211 Table

  1. Assess SSIS packages with Database Migration Assistant.
  1. Import projects to Azure SQL MI.
    • Only project deployment model supported with SSIS in ADF.
      • Export your packages as .ispac files and move them to a location where your Azure SQL MI can import them from.
  1. Configure packages with new connections.
    • Connection properties can be edited in SSMS or ADF.
  1. Schedule package execution in ADF.
    • Open ADF Studio to create a pipeline to execute/schedule your SSIS package.

Image SSIS Image 5

    • Under “General” tab, give the activity a meaningful name. Click on “Settings” tab, pick the Azure-SSIS IR created earlier, then navigate to the target package.

Image SSIS Image 6

    • You can edit the connection properties under “Connection managers’ tab. Click on “Debug” to run the pipeline. Click on “Add trigger” to schedule the pipeline. Click on “Properties” to give the pipeline a meaningful name.

Image SSIS Image 7

    • Configure a Trigger to set a schedule for the pipeline.

Image SSIS Image 8

  1. Set up alerts in ADF by creating a new alert, adding criteria, alert logic and notifications.

Image SSIS Image 9

Learn more

 

Aarti Riley Cloud Solution Architect, Data & AI

Read next

3 comments

Discussion is closed. Login to edit/delete existing comments.

  • Pete DiPaolaMicrosoft employee 0

    This is excellent content that will benefit our Federal and Commercial customers and partners! Thank you Aarti!

  • Joshua LentMicrosoft employee 0

    Thanks for putting this together Aarti, I’ll be able to use it very soon with a customer of mine.

  • R3S Services 0

    This is excellent content that will benefit our Commercial customers and partners! Thank you Aarti!

Feedback usabilla icon