Azure Data Factory - Pipeline always fails at the second connection to On-Premise data source

Question

I have SSIS Package that pulls data from On-premise data Source and push data to Azure Database. In that package, I have 3 data flow tasks, each of them connect to one db on On-premise data Source. SSIS Package has been deployed to Azure SQL DB (SSISDB) and ADF Pipeline is created with this package.

I configure to access on-premises SQL Server from Data Factory Managed VNet using Private Endpoint, as link here.

Here is my VM properties:

If I run the package in Visual studio, it works well.

If I run the package on ADF pipeline (using Azure-SSIS IR), it always fails at the second or the third data flow with the error:

Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.\r\nAn OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Login timeout expired".\r\n\n

Any suggestion how to fix / debug it further ?

Answer 1

Thanks for posting your question in the Microsoft Q&A forum.

You can try the following steps:

• In the SSIS package, locate the connection manager for the SQL Server database. increase the Connection timeout property value to allow more time for the connection to be established.
• Review the SQL queries executed within the SSIS package. Ensure that the queries are optimized and do not take an excessive amount of time to execute. Consider breaking down complex queries into smaller, more manageable parts.
• Use SQL Server Management Studio to check for any blocking processes or deadlocks that might be causing delays in the database connection. if found, resolve the blocking issues or deadlocks.
• Use SQL Server Performance Monitor or other monitoring tools to check for any performance bottlenecks or resource constraints on the SQL Server or the machine running SSIS.

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful