Create dynamic connection using fetched records

[Anonymous]

Hi,

I'm trying to evaluate Talend for Data warehouse which will be built on MariaDB or PostgreSQL. I have a config table in data warehouse which has connection parameters required to create or establish a connection to SQL Server. Each record has full information (like IP, port, userid, password, database and schema) about the instance to be connected.

Is it possible to iterate through the config table, establish a connection and execute a data transformation task on each instance?

Note: I'm using Talend Open Studio for Data Integration v7.0.1

[fdenis]

Yes It's Possible.
Create a job with connection values in context.
Then Create an other job who read connection data use flow to iterate (tFlowToIterate) to call the previous Jaob with connection data.
Regards

[fdenis]

it's job not joab!

[TRF]

For sure, as soon as each required element is furnished using à global or context variable based on the content of the configuration table.

[Anonymous]

Thanks for the response.

If I understand correctly, 'context' is something like an environment which has variables set differently for each environment. This environment or context is saved and can be reused.

In my case, the connection need not to be saved because the connections change periodically and I would end up having too many contexts.

Do we have something like the global variables, where I set the variables values with connection parameters for each iteration and execute the job?

Is this possible?

Thanks

Deepa

[fdenis]

context is the context used to ru a job.
when you run a job inside an other job you can set context values.
there is also global variable who are used to get software or components values.
Regards

[Anonymous]

@fdenis

Thanks, I was able to get your idea.

I created a base job which does the transformation and created another wrapper job which calls the job after assigning the context values.

In wrapper job, I'm able to iterate the base job but could NOT find a way to assign the context values during the execution time.

Could you please share some resources where I can assign the context values during the execution time?

Note: I tried the 'tContextLoad' but it seems using only one key and value pair, however, in my case I have many connection parameters like IP, port, userid, password, database, schema and additional parameters which helps to connect to encrypted SQL Server.

[fdenis]

your connections info are in a file so:
TfileInput --> onComponentOk --> tflowToIterate --> tRunJob
define connection globalMap values into tflowToIterate and use this info to set job Context in to tRunJob.
Regards,

[Anonymous]

Hi,

I tried to create a job as suggested above but there is some miss configuration and throws error.

1. I created a base job which is working as expected.

2. I created a wrapper job which calls the base job on iteration.

3. In wrapper job, for 'tFlowToIterate' component I tried mapping relevant fields with the context variables but fails.

I have attached the properties of 'tFlowToIterate' and screenshot of 'wrapper job' along with the error logs.

Could you help me resolve the issue?

errLog.txt
tFlowToIterate.PNG
wrapper.PNG

[fdenis · Accepted solution]

into tFowToIterat you have to define globalMap variables who are going to be reuse to set context in tRunJob.
the name of globalMap value must be strings.
When you write cotext.toto java is lookin for the value of the context named toto.
Surround key by "
Key="context.totoOrWhatYourWant"
value=row1.toto (it's the value of toto in row1 link. do not use just toto).

in tRun
retive value of globalMap like :
(String)globalMap.get("context.totoOrWhatYourWant")

regards