TRANSACTION ISOLATION LEVEL on Talend

[SDHON]

Hello,

We are using TOS 7.3 connected to ssms.

Randomly, connection reset error appears in few of the Talend Jobs launched via JOE (JobScheduler Object Editor).We tried to reschedule the jobs to avoid parallel launch as much as possible. Massive data purge did reduce the job execution time and frequency of the issue. However, still getting the error on and off now. 

One potential avenue, given we do not have transactional operational on these tables is to set our transactions to 'ReadUncommitted' isolation level to prevent any blocks/locks..On database level, all is already set to ReadUncommited. However, the isolation level is shown as ReadCommited - while the Talend jobs are running, another script (uncommited query from sys.dm_exec_sessions) runs in parallel at different interval to extract and insert in a log table which gives details on all current running jobs (host, program, login, datetime, transaction level, status etc. -- see attached snapshot. 

Is there a way to globally set/force TRANSACTION ISOLATION LEVEL on Talend itself please?

Many thanks.

[Anonymous]

I'm afraid not. This is SQL server functionality that is intended to be used/set during an open session.

[SDHON]

Well noted. Thank you.

[Brian_G]

I can confirm that setting the transaction isolation level using a tDBRow component is a solution that works. I had created a job that dynamically loads a list of tables from SQL Server. I was able to do a Pre job connect and then started my job with a tDBRow that ran the "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" statement. Seems to work well and the job is loading over 70 tables without issue.