Iteration Loop

Highlighted
Six Stars

Iteration Loop

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?


Accepted Solutions
Employee

Re: Iteration Loop

Hi,

 

     Why don't you try like below?

image.png

 

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

View solution in original post


All Replies
Employee

Re: Iteration Loop

Hi,

 

     Why don't you try like below?

image.png

 

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

View solution in original post

Six Stars

Re: Iteration Loop

Thanks so much Nikhil!
Six Stars

Re: Iteration Loop

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?

 

 

 

Employee

Re: Iteration Loop

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

Six Stars

Re: Iteration Loop

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

2019 GARTNER 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

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog