Azure SSIS Package - Execute SQL Task

1
votes

I have created a simple SSIS package with "Execute SQL Task". When I am trying to execute the package in Azure, it fails with the below error:

Error: Failed to acquire connection "my connection". Connection may not be configured correctly or you may not have the right permissions on this connection.

Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Login timeout expired". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Named Pipes Provider: Could not open a connection to SQL Server [53]. ".

However, it works fine when I run it with some other task. Can someone tell me what could be the issue? I am guessing SSIS package execution from Azure does not support "Execute SQL Task".

Any suggestions would be greatly appreciated.

1
azuressisazure-data-factoryazure-integration-runtime
Actually, where the SQL task query data from, on-premise SQL Server? – Leon Yue
can you please elaborate works fine when I run from some other task – Venkataraman R
@LeonYue The SQL task queries data from Azure SQL Managed Instance. I am using SSIS-Runtime Integration that uses a self hosted managed instance as proxy. – Karthik Doure
@VenkataramanR Instead of SQL Task, I have used a Data flow task that copies the contents of one table to another table. Both the tables are from Azure SQL Server Managed Instance. – Karthik Doure
It works when I use Azure SQL database instead of Azure SQL Server Managed Instance. – Karthik Doure

1 Answers

1
votes

After going through lot of documentation online, I have found that: SSIS Package execution through an SSIS Integration Runtime using a proxy supports only Data flows.

Refer Limitation section in the below link:

https://docs.microsoft.com/en-us/azure/data-factory/self-hosted-integration-runtime-proxy-ssis

To fix this I have create another SSIS Integration Runtime that joins the same VNet as Azure SQL Server Managed Instance.