intermittent DB connection issue with SSIS Package

sajesh k 1 Reputation point
2022-10-31T05:38:44.377+00:00

Dear Experts,

We have an intermittent Database connectivity issue with the Data flow task in our SSIS Package. Sometime Job is working as expected but some other time it is getting failed to get the Database connection. Delay validation property is set as True for the data flow task. We are getting the below 2 different types of issue with different Job execution.

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

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

Connection string, we configured as a variable in project.param and this parameter used as a connection string expression in OLEDB Connection manager.

We have this issue in production environment, same package is working in UAT without any failure. This Job was working without any issue in production environment till 20th October 2022, from 21st onwards we are getting this weird issue and not able to figure out the reason for this issue. Can anybody help me to figure out the root cause of the issue?

Thanks in advance.
Regards,
Sajesh.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,456 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 33,126 Reputation points
    2022-10-31T07:19:20.42+00:00

    Hi @sajesh k ,

    Could you please run the agent job and set the Run64BitRunTime to the opposite value (True=>False or False => True) to see if it could resolve the issue.

    255586-untitled.png

    Please also make sure that the proxy account has the full permission to access the servers.

    In addition, it seems that there is an intermittent network issue. You may contact the network team to investigate it.

    Regards，

    Zoe Hui

    If the answer is helpful, please click "Accept Answer" and upvote it.

    0 comments
  2. sajesh k 1 Reputation point
    2022-11-09T08:16:58.97+00:00

    Dear Zoe Hui,

    Thank you for your response. I don't think the issue related to the 32 bit or 64 bit run time or our proxy account.it was worked perfectly till 25th October then started the issue.
    In our Package we have many Data flows and those DF's are execute in sequence. Sometime the job is failing during the last DF execution. Looks like something related to network or Server. But the respective team is saying there is no issue. But in the report, we can see error "logintime out expired" .Please refer the attached screenshot. Not sure how we can figure out the root cause.

    Thanks.
    Regards,
    Sajesh.258567-ssisissue.gif

    0 comments
  3. Yitzhak Khabinsky 25,026 Reputation points
    2022-11-10T13:34:08.173+00:00

    Hi @sajesh k ,

    I would suggest switching from Microsoft SQL Server Native Client 11.0 to Microsoft OLEDB Driver:

    oledb-driver-for-sql-server

    Here is an excerpt from the official documentation:

    Important

    The previous Microsoft OLE DB Provider for SQL Server (SQLOLEDB) and SQL Server Native Client OLE DB provider (SQLNCLI) remains deprecated and it is not recommended to use either for new development work.

    0 comments