SSIS: AcquireConnection errors after migrating to Azure VM

user_7076117 0
Viewed 148

Recently we migrated from on-prem to Azure VM based SQL Server instance (on-prem 12C/128GB, Azure VM 16C/128GB). We migrated SSIS packages from 2008 to 2016.

We have an SSIS package (runs on the same VM as SQL Server) that runs 15 very similar parallel sequence containers. Each container selects data from its own EXT tables, applies some transformations, and inserts it to one STT table (common for all containers). Both types of tables are in the same database. Sometimes while executing the package we randomly encounter an error:

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "DBCONN_STG_OLEDB" failed with error code 0xC0202009.

There is no consistency, each time error occurs on different tasks. The error didn't appear in the old environment.

I suspect that there may be an issue with parallel connections to the database. Error is hard to reproduce since it happens randomly.

ssis sql-server
1 Answers

The issue occurs because of the behavior change in how OLE DB handles parameters in Microsoft SQL Server 2012. This change is by design. To resolve this problem, rewrite the query to add an additional explicit cast to the original data type. Then, sp_describe_undeclared_parameters returns the correct, expected type. To resolve this issue in an example query that is described in the "Symptoms" section, update the query as follows:

SELECT   mydate 
FROM     dbo.myTable
WHERE mydate >= convert(char ,dateadd(year,-1, cast( cast( ? as char(8)) as datetime)))
user_3312000
Related