Iterate From a SQL Server Input query result (and Run components for each date )

[Anonymous]

Good Day Everyone , 

I'm new to talend and i'm having a bad time  trying to resolve this particular Issue and i would like to have the best advice i can , since i've been monkey-testing for 2 days without luck .

I've got this process that needs to be skipped on holidays , and execute the holiday report on the next business or working day with the report that runs normally on non-holidays , no reports combined just running the report for each date  that was skipped , this is based  based on a date-flag stored on a sql server table.

Since there are times when there are consecutive days , such as 25 th and 26th December and cases like that ,i need to get a list of the days that are being skipped and execute them all on the next working day. I resolved the part of the dates needed through a tMSSqlinput which outputs all the dates that are going to be iterated ,  but i don't know how to fill a list and make the report iteration , modifying the date variable , send the current value as an input , if i should use a tloop , a tforeach , or use tflowtoiterate to execute the report n times for each date before it finishes .

my flow so far is 

Connection --> validate if holiday  --> If no  --> Check Yesterday  --> get all dates that we're holiday -->                                                                  |

                                                      |

                                                      --> if yes Skip  --> end 

the next part of the job its the one i am  dealing with after that the part i need to execute is.

im guessing it should be like this  

SQL server Output Date List   --> flowtoiterate  --> tloop with for each date   --> and tflex maybe to increase or decrease the dates 

the the part of the report that already works and generates the report but will be executed for each date 

Receive date input --> process reports --> Validation --> Send email --> kill connection --> End

Any advice or suggestion would be appreciated since i don't know how it should be done with all the stuff I've searched.

[Anonymous · Accepted solution]

You're definitely on the right track here -- but you may just be over-thinking things a bit.

first ensure your report job accepts the date to run as a context parameter.

if you can return a list of dates to run the "holiday reports" with a database query (with one date to run per row), then you'd just need to use the following component flow to execute the report job once per date:

tMSSqlInput ---row--->tFlowToIterate---iterate--->tRunJob

In the tRunJob component, you have a table where you can populate the values for context parameters. In the date to run context param, write the following to 

retrieve the date from the query output:

 ((Date)globalMap.get("row1.date_to_run"))

This is assuming your tMSSqlInput has a schema column named "date_to_run" of type Date. The tFlowToIterate component will create a global map key that you can retrieve for each column of your schema.This globalMap value will be updated once per row and will hold the value of the query output. 

[Anonymous]

thanks , that worked like a Charm , i'll cross my fingers that this works through Talend Administration Console