SSIS from Premise to Azure connection login timeout

Question

SSIS from Premise to Azure connection login timeout

sqlfriend

SSC Guru

Points: 52781
More actions
July 17, 2018 at 10:56 am

#373588

I setup a nightly job to load data from a database on premise to Azure.
The SSIS runs successfully intermittently, but sometime it fails.
The error is all about :
[Destination 2 [112]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ourcloudserver.database.windows.net.SSDEquipment" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
login timeout expired, unable to complete login process due to delay in login response.
So I guess this is an issue with connection issue time out , how can I fix this?
This package only runs totally 2 and half minutes.
How come it lost the connection in the middle. Our network has no problem.
I can login to the azure portal all the time.
And I run all this on the same machine that allow access to the azure portal
Thanks
DataSource=xxxxxxxxxxx; User ID=xxxxxxxxx; Initial Catalog=xxxxxxxxxxx; Provider=SQLNCLI11.1;Persist Security Info=True; Auto Translate=False; Use Encryption for Data=True;

Connection after modification:

DataSource=xxxxxxxxxxx; User ID=xxxxxxxxxx; Initial Catalog=xxxxxxxxxxxxx; Provider=SQLNCLI11.1;Persist Security Info=True; ConnectTimeout=60; Auto Translate=False; Use Encryption for Data=True;

mark.humphreys SSC Eights! Points: 867 More actions · Answer 1

I am having similar issues. Has anyone found a solid solution to this. Currently I am changing the provider to SQLOLEDB.1 and testing this.

It is happening across a number of our SSIS packages being called from SQL Agent. Being an intermittent issue it is challenging to troubleshoot.

mark.humphreys SSC Eights! Points: 867 More actions · Answer 2

In case anyone else does have this issue.

The next thing we tried after finding a thread about this on another forum was to flush the DNS cache. This was done on Friday and all previously failing jobs ran successfully over the weekend. We will continue to monitor this and see if this does solve the issue for us.
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/00b93d44-b497-4367-a3cf-99dfd42f0f66/sql-native-clienttcp-provider-timeout-error-258?forum=sqldataaccess

mbruno961 SSC Rookie Points: 26 More actions · Answer 3

I had the same problem too. We are using the project deployment mode and look at the SSIS catalog report information to find the execution details. What happens is that the packet does not really go into process on failure days, the failure happens in the validation of the connections. We changed the DelayValidation property of the connection to TRUE and we had success in the executions since Saturday. I update this information if it fails again.

mbruno961 SSC Rookie Points: 26 More actions · Answer 4

Modifying the "DelayValidation" property did not work for me. The error occurred again after the modification.

But I found a resolution. What happens in my case is that the "Connection Timeout" property of Connection Manager, when verified via wizard, is set to 0, which would cause the task to have unlimited time to connect to SQL Server. However, upon checking the Connection String of the connection, I noticed that the "Connect Timeout" property was not determined (consequently makes the connection string default timeout equal to 15 seconds, which was not enough in our current infrastructure between Azure and on-prem). According to the SSIS logs, it was just the time my packages ran until they failed, when that happened.

To adjust the "Connect Timeout" property, I modified the property as needed (in this case 60 seconds) and save it. After the modification the errors did not occur again.

Connection before modification: