How to connect SSIS (on prem) to an Azure Managed Instance

Question

Is it possible to connect SSIS that resides on-prem to an Azure Managed Instance?

Answer 1

Yes, it is possible. You just need to download and use the following driver.

Microsoft® OLE DB Driver 18 for SQL Server®
The Microsoft OLE DB Driver for SQL Server provides native connectivity from Windows to Microsoft SQL Server and Microsoft Azure SQL Database.
https://www.microsoft.com/en-us/download/details.aspx?id=56730

Answer 2

Hi @LM ,

This article describes how to run a SQL Server Integration Services (SSIS) package by using Azure SQL Managed Instance Agent. This feature provides behaviors that are similar to when you schedule SSIS packages by using SQL Server Agent in your on-premises environment.

With this feature, you can run SSIS packages that are stored in SSISDB in a SQL Managed Instance, a file system like Azure Files, or an Azure-SSIS integration runtime package store.

Please refer to Run SSIS packages by using Azure SQL Managed Instance Agent.

Best Regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Thank you for your replies. I need to connect to an Azure managed instance from a sql agent job that resides on-prem. I can connect from visual studio by specifying the connection string like this
Data Source=**XXXXXX.database.windows.net;**User ID=XXXXXX;Password=XXXXXX;Initial Catalog=XXXXX;Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;
The package runs great from Visual Studio. When I try to run it from Sql Agent, ServerName is a package parameter and I have tried different combinations of the connection string shown above and I can't get it to work.
The job goes to success immediately and doesn't bring any data from Azure.