[resolved] how to control commit or rollback in line level

One Star

[resolved] how to control commit or rollback in line level

Hi,
I want to do something normal like insert two outputs of a tMap into two differents tables, but I want to control commit or rollback for every line of the file(if there is no reject for both the two tables, I commit, if not, rollback ), not at the end of all lines.
Is this possible? If yes, I should based on which condition to know if there is no rejects for both two tables?
Thanks in advance.

Accepted Solutions
Community Manager

Re: [resolved] how to control commit or rollback in line level

Hi
The globalMap is only available in the job itself, to pass a row to child job, you have to use context variables. I show an demo job in topic:
http://www.talendforge.org/forum/viewtopic.php?id=1654
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business

All Replies

Re: [resolved] how to control commit or rollback in line level

sure. use an iterate link to process one row at a time.
so,
tFileInputDelimited--row-->tFlowToIterate---iterate--->tFixedFlowInput---row->tMap
in the tFixedFlowInput, retireve the column values that the tFlowToIterate stores from the globalMap. so if you have a column "some_data" in the row1 input to the tFlowToIterate, it will be in the global map with the key "row1.some_data". The tFixedFlowInput will (by default) output one row per iteration, and feed it to the rest of your job.
One Star

Re: [resolved] how to control commit or rollback in line level

Thanks a lot for your help.
But I still have a question, if I do something like this (see shotscreen), what should be the if conditions?
I tried the conditions:
for commit:
(Integer)globalMap.get("tMSSqlOutput_1_NB_LINE_REJECTED")<=0 &&
(Integer)globalMap.get("tMSSqlOutput_2_NB_LINE_REJECTED")<=0
for rollback
(Integer)globalMap.get("tMSSqlOutput_1_NB_LINE_REJECTED")>0 ||
(Integer)globalMap.get("tMSSqlOutput_2_NB_LINE_REJECTED")>0
But it does not work.
One Star

Re: [resolved] how to control commit or rollback in line level

the sceenshot
Community Manager

Re: [resolved] how to control commit or rollback in line level

Hi
As John said, iterate each row and pass each row to child job, the job design should be:
parent job:
tFileInputDelimited--main--tFlowToIterate--tRunJob_1
child job:
tMssqlConnection
|
onsubjobok
|
tFixedFlowInput--main--tMap---main--tMssqloutput_1
---main--tMssqlOutput_2
---onsubjobok---tMssqlcommit
---onsubjoberror--tMssqlRollback.
On tRunJob_1: call the child job and pass each row to child job, uncheck the box 'die on error'
on tMssqloutput_1 and tMssqloutput_2: check the box 'die on error'
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] how to control commit or rollback in line level

Thanks so much.
I have a last question, in the parent job in tFlowToIterate I store a value into the globalMap, and in the child job I can't retrive it in the tFixedFlowInput. I suppose that's because the parent job and child job don't share the same globalMap. So how can I get the value in the child job? I checked "transmit whole context" in tRunJob.
Community Manager

Re: [resolved] how to control commit or rollback in line level

Hi
The globalMap is only available in the job itself, to pass a row to child job, you have to use context variables. I show an demo job in topic:
http://www.talendforge.org/forum/viewtopic.php?id=1654
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] how to control commit or rollback in line level

Yes it works.
Thanks a lot.
Community Manager

Re: [resolved] how to control commit or rollback in line level

Hi
Thanks for you feedback, I am glad to see that you get it works now!
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch