Keep loop working on Sql Connection Error

One Star

Keep loop working on Sql Connection Error

Hi everyone,
I started a little time ago working with talend and im still getting used to so im sorry if this is a dumb question.
The problem is simple i think, i have a bunch of different MSSql databases with different ips but, with the same schema and tables.
I need to connect to each one of them, execute one select sentence (i already have it and works fine) and then retrieve the info it gets to an excel file.
I tried at first with more than 50 tMSSql inputs, and connections, was the easy way, and it stopped working with memory problems due to the high amount of components.
Then i changed it all to the actual model that is quite simple and efficient.


On the first excel input i have a list of all ips, passwords, and all the config parameters the component MSSqlinput use
Then the iterate component do his job and collect all the data and throws to the MSSql connection component
And here is the problem, i want it to ejecute the select if it can connect to the IP (wich is actually doing) or, if it cant connect, just throw the error and go to the next ip on the list.
Now when i execute it and one ip is not working it just stop the execution and leaves all the following ips without proccesing.

Anyone knows a workaround for this? i have been trying A LOT of other aproaches to this problem for more than a week and all ended up the same, when one ip is not working everything stops.

BTW, the java error when one ip is not working is the next, in case it is too usefull.

Exception in component tMSSqlConnection_1
java.sql.SQLException: Network error IOException: Connection refused: connect
Ten Stars

Re: Keep loop working on Sql Connection Error

Try putting the connection and query code into a child job (another Talend job called by using a tRunJob). You can pass your connection values through using context variables. Make sure you untick the "Die on child error" checkbox on the tRunJob. 
One Star

Re: Keep loop working on Sql Connection Error

Hi rhall and thanks for your answer,

Here is where i am now, i cant add context parameters to the runjob component.




And the subjob is just a copy paste of the previous check and execute SQL query




Any hints about this?

Thanks for your help.
Ten Stars

Re: Keep loop working on Sql Connection Error

You need to create the context variables you want to use within your child job. Then you can select them to be populated when you call it in your parent job.

I believe that simply copying and pasting the components into the child job should work.
One Star

Re: Keep loop working on Sql Connection Error

After some texting and a bit of research on talend documentation about context variables and their use i have finally finished the program.

Now works like a charm, i just need to finish the error logging and im done.

Thank you so much for your help rhall.

Cheers.

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 2

Part 2 of a series on Context Variables

Blog

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