Iteration Loop

[jimbo20814]

Hi, I know this probably common/simple but I'm new to Talend DI. What component would you use to traverse through records via a SQL select to do a body of work?

For example let's say there is a series of tasks that takes @companyID as variable. Then there is a SQL table that contains the companyIDs to be processed. So for example there are five companyIDs returned from select statement. So these series of tasks would need to be executed five times, with @companyID changing five times.

Oh also, then how are errors handled within iteration loop? So let's say it's to traverse companies 1 to 5 in that order. If company 3 processing fails, will DI then exit the program? Or can the job be configured to move on to company 4 processing. If transaction is setup, will it rollback the tasks for company 3?

[Anonymous · Accepted solution]

Hi,

     Why don't you try like below?

First tDBInput fetches the list of company ids and you can use it as parameters in the second tDBInput in iterative fashion. The tFlowtoIterate will make sure that the data is stored to a variable linked to the component and you can use this variable to assign the value in next section after iteration. 

On Component OK and On Component Error will make sure that the data is either committed or rollback for each iteration. There are lot of examples in the Talend community which can give you idea about the usage of tFlowtoIterate and subsequent assigning of its variables inside iteration.

Could you please try it out and let us know whether you are able to proceed?

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

[jimbo20814]

Thanks so much Nikhil!

[jimbo20814]

Hi Nikhil, can you determine why the records did not make it to the destination by the below screenshots attachments? Maybe because of the error? What's the error?

Capture1.PNG
Capture2.PNG

[Anonymous]

Hi,

   It says DB connection got closed for the component. Are you using tOracleConnection or tOracleCommit component? If yes, could you please check in Advanced settings whether you have selected the option to close the connection?

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

[jimbo20814]

No, I'm using tDBInput(Microsoft SQL Server) and tDBOutput(Microsoft SQL Server)