Reading database connection info dynamically from a table

Highlighted
One Star

Reading database connection info dynamically from a table

[font=Calibri][size=3]Hi[/size][/font] guys,

[font=Calibri][size=3]Is there any way to read the database connection information (host, port, database, username and password) dynamically from MySQL table and set these values to context variables.[/size][/font]
[size=3]Thanks in advance of your cooperation.[/size]
[size=3]WY.[/size]
Community Manager

Re: Reading database connection info dynamically from a table

Yes, you can use the tContextLoad to achieve this. However, in the example you give you will need to hardcode the DB connections of the DB you wish to acquire the context variable values from. This tutorial should give you an idea....
http://www.talendbyexample.com/talend-load-context-example.html
One Star

Re: Reading database connection info dynamically from a table

[size=3]Thank you very much Richard for your quick reply.[/size]
[font=Calibri][size=3]Actually, I am extracting the data from MS SQL server database and unfortunately the password is changing every Sunday due to security roles in my company, that is way I need to read the connection information dynamically from a table as it is not logic to open the solution every Sunday to change the password.[/size][/font]
[font=Calibri][size=3]I used to do this in SSIS using configuration tables, so I am wondering if we can do this on Talend also.[/size][/font]
[font=Calibri][size=3]Thanks.[/size][/font]
[size=3]WY.[/size]
One Star

Re: Reading database connection info dynamically from a table

[size=3]Richard,[/size]
[font=Calibri][size=3]I found a solution for this,[/size][/font]
[font=Calibri][size=3]All what we need is to create a table in the database with two fields key and value (in small letters), then add the keys and values for the information of the connection as attached.[/size][/font]


Then create the same keys above as contest variables (even as global context) in Talend and after using tContextLoad it overrides the values from the tables dynamically at the run time.
Thank you very much for your support and help and for the topic you’ve sent above.
Thanks.

[size=3]WY.[/size]

Community Manager

Re: Reading database connection info dynamically from a table

Hi WalidYoussif,
Your solution is essentially what I was suggesting, but I am curious as to where you are keeping your database credentials for the database that you have created your context variable (key/value) table? If your database passwords change every Sunday, I am assuming that will be for ALL databases.

If you get this problem you can keep context variable data on flat files as well (in the same way as in your database). So you could have a flat file with the credentials of your database that holds the rest of your context variables. Security is often a concern here, so you can encrypt the key and value in the file, decrypt it within the Talend job and then use them.  

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

Introduction to Talend Open Studio for Data Quality

Find out about Talend Open Studio for Data Quality

Watch Now

Enabling Data Governance

Learn how to enable Data Governance

Watch Now

The Definitive Guide to Government Data Quality

Take a peek at the definitive guide to Government Data Quality

Read