slow ETL job

Question

Hi Experts,

I need some piece of advice on how to about this below scenario.In our environment we have ETL solution called BODS to load data into a data warehouse.
Application team says, one of the ETL job is running slow. Basically, it is not a SQL Agent Job. ETL tool has its own job creation and scheduling capabilities. When they run BODS ETL job, they connect various data sources, apply some data massaging logic using BODS inbuilt data flow transformation and finally it gets loaded into SQL Server tables. All the queries are generated from the GUI tool itself in the form of transformations.Now, the app team needs some help from Database admin team to fix long running ETL job.They say the job runs fine, sometimes long enough and sometimes it fails with below error.

[LIBODBCHDB DLL][HDBODBC] Connection not open;-10807 Connection down: [89006] System call 'send' failed, rc=10054:An existing connection was forcibly closed by the remote host {10.xxx.xxx.x:portno} {ClientPort:51555}>..Error Context :Dataflow DF_Load_CUSTOMER_ACCOUNT_INFO_DUPLICATES.

In such scenarios how to go about troubleshooting such kind of SQL problems. They don't know what is exact SQL stmt which is causing the problem.
In such situations, being a SQL DBA, what tuning efforts we can put in on such tricky SQL tuning issues? what questions we might probably ask to drive such issues to a common solution.We suggested them to involve the networking team to see if there is any issues to force the sql connection to get killed. Looking for suggestions to better handle such tool generated issues? Do we need ask them to open a ticket with the respective ETL vendor to get some recommendations and what kind of help can a SQL DBA can provide value in these cases?Because we dont understand their tool and they dont understand what actually we are asking for ? (eg: not knowing the exact SQL query which is causing the issue).

Best Regards,
Bob

Answer 1

Your first point of contact should be SAP BODS support. They would be the best to help troubleshoot the issue inside of BODS.

From a general perspective, these errors are APPLICATION errors, not SQL Server errors. There is normally nothing anywhere in SQL Server which will show an error or will help diagnose this kind of application problem. The best you can do is look at overall health of the SQL Server and if there was any outage/failover/maintenance, network outage, VPN issue, etc. during the problem time.