topic Re: Create dynamic connection using fetched records in Talend Studio

Create dynamic connection using fetched records

Anonymous — Mon, 27 Aug 2018 14:05:38 GMT

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

Re: Create dynamic connection using fetched records

fdenis — Mon, 27 Aug 2018 14:36:14 GMT

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

Re: Create dynamic connection using fetched records

fdenis — Mon, 27 Aug 2018 14:36:43 GMT

it's job not joab!

Re: Create dynamic connection using fetched records

TRF — Mon, 27 Aug 2018 14:36:46 GMT

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

Re: Create dynamic connection using fetched records

Anonymous — Tue, 28 Aug 2018 06:56:52 GMT

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

Re: Create dynamic connection using fetched records

fdenis — Tue, 28 Aug 2018 08:02:32 GMT

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

Re: Create dynamic connection using fetched records

Anonymous — Tue, 28 Aug 2018 14:37:19 GMT

@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.

Re: Create dynamic connection using fetched records

fdenis — Tue, 28 Aug 2018 14:54:35 GMT

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,

Re: Create dynamic connection using fetched records

Anonymous — Thu, 30 Aug 2018 11:44:22 GMT

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

Re: Create dynamic connection using fetched records

fdenis — Thu, 30 Aug 2018 12:45:47 GMT

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