CODEWITHSUNDEEP
sql-serverazuressisazure-sql-database
joechip
joechip

Reputation: 359

SSIS Connection Manager - Azure SQL Database intermittent failure

We have an SSIS job that connects to a hosted SQL Database in Azure. Occasionally we get error messages when the connection manager is unable to acquire a connection.

Example error messages:

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "AzureCloud" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Login timeout expired". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.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 10.0" Hresult: 0x80004005 Description: "Named Pipes Provider: Could not open a connection to SQL Server [53]. ". End Error Error: 2017-04-28 01:03:48.00 Code: 0xC020801C Source: Copy to stage Lookup [580] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Cloud" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2017-04-28 01:03:48.83 Code: 0xC0047017 Source: Copy to stage SSIS.Pipeline Description: component "Lookup" (580) failed validation and returned error code 0xC020801C. End Error Error: 2017-04-28 01:03:49.65 Code: 0xC004700C Source: Copy to stage SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2017-04-28 01:03:50.50 Code: 0xC0024107 Source: Copy to stage Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:00:12 AM Finished: 1:03:50 AM Elapsed: 218.339 seconds. The package execution failed. The step failed.,00:03:38,0,0,,,,0

Is there an Azure SQL Connection Manager that can use connection resiliency to retry acquiring a connection in the event that a network event (e.g. increased latency on the public internet) occurs?

Thanks!

Upvotes: 8

Views: 1072

Answers (1)

Tarek Salha
Tarek Salha

Reputation: 384

There is no specified azure sql connector with more resiliency against transient errors. You need to implement this by yourself.

If you are using ssis packages in connection with sql server agent jobs, then you should consider using the retry feature of the agent's job steps.

enter image description here

if you are otherwise starting your packages from custom code, then utilize a retry library. For e.g. in c# there is a package called Polly, that handles retry policies very well.

Upvotes: 0

Related Questions