Occasional error for a SQL Server Agent job that calls SSIS packages to read SAP data - error code 0xC0014009

Question

Occasional error for a SQL Server Agent job that calls SSIS packages to read SAP data - error code 0xC0014009

pmscorca 1,052

Hi,

I’m monitoring a SQL Server Agent job that calls some SSIS packages deployed on the SSIS catalog, in order to load SAP data into a SQL staging via ODBC. This job goes in error one time each 4-5 runs with success.

In particular, the error is “The AcquireConnection method call to the connection manager SAP_HANA_ODBC failed with error code 0xC0014009”.

The ODBC driver for SAP HANA is HDBODBC32 vers. 2.4.194; I cannot use the 64-bit one because it doesn’t function, and so the job steps run to 32-bit.

The version/edition of SQL Server is Microsoft SQL Server 2017 (RTM-CU31) (KB5016884) - 14.0.3456.2 (X64) Sep 2 2022 11:01:50 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: ) (Hypervisor).

Each job step runs as a proxy based on a credential with the identity having the sysadmin role in SQL Server. The job owner is the credential identity.

The related ODBC data source tested from the ODBC administrator always connects to SAP with success and uses an ad-hoc SAP user.

No evidencies in the SQL Server log, the SQL Server Agent log and the system event viewer.

Now, any suggests to me in order to run this job with success always? Any incompatibilities between SQL Server 2017 and the ODBC for SAP HANA? Any permissions to add for the above identity credential? Other?
Many thanks

1 answer

Your answer

Answer 1

ZoeHui-MSFT 41,491

Hi @pmscorca,

There is no more error message for us to narrow down the issue.

Please try to set Delay Validation property of Data Flow tasks to TRUE.

If this does not work, could you please enable logging to catch more details?

Regards，

Zoe Hui

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

pmscorca 1,052 Reputation points

2023-06-08T07:59:32.3133333+00:00

Hi @ZoeHui-MSFT thanks for your reply.

I get the error message reading the SSIS catalog reports. I remember that the issue occurs occasionally.

I will try to set Delay Validation property of Data Flow tasks to TRUE; but do I need to change the value for ValidateExternalMetadata property of ODBC sources inside the data flow tasks, too?
Thanks
ZoeHui-MSFT 41,491 Reputation points

2023-06-08T08:14:00.5566667+00:00

Hi @pmscorca,

Because metadata validation can be expensive (large table with a lot of columns, for example), the ValidateExternalMetadata flag can set to false to disable this validation. Typically, you'd only do this when you are certain that your package and underlying table/view/query will remain in sync.
pmscorca 1,052 Reputation points

2023-07-03T17:07:42.6133333+00:00

Hi @ZoeHui-MSFT I've set Delay Validation property of Data Flow tasks to TRUE, but after 5 job runs the error occurs agains. I've tried to restart the SQL Server service and the SQL Server Agent service unsuccessfully; also restarting the SQL machine hasn't solved the issue.

Moreover, I've enabled the SSIS logging obtaining these message respect to "OnError" event: "Calling the AcquireConnection method to the SAP_HANA_ODBC connection manager failed with error code 0xC0014009. There may be additional error messages before this one with additional information about why the AcquireConnection method call failed." and "Unable to complete validation of ODBC Source. Returned error code: 0x80004005."

Now, any other (urgent) suggests to me, please? Thanks
pmscorca 1,052 Reputation points

2023-07-03T17:21:47.1066667+00:00

Hi @ZoeHui-MSFT moreover for the SSIS logging I've enabled the OnPostValidate, the OnPreExecute, the OnPreValidate and the OnTaskFailed events without obtaining any other useful error messages.

Share via

Occasional error for a SQL Server Agent job that calls SSIS packages to read SAP data - error code 0xC0014009

1 answer

Your answer