Share via

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

LM 1 Reputation point
2021-02-10T19:04:04.59+00:00

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

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,467 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Yitzhak Khabinsky 25,201 Reputation points
    2021-02-11T15:00:16.127+00:00

    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

    1 person found this answer helpful.
    0 comments
  2. Monalv-MSFT 5,896 Reputation points
    2021-02-11T02:56:33.09+00:00

    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.

  3. LM 1 Reputation point
    2021-02-25T02:37:40.133+00:00

    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.