[resolved] How to run a .sql file in Talend

[Anonymous]

Hi,
I have a .sql file which contains my ETL scripts. How can i trigger it directly from Talend.
I can run the statements directly from tMSSqlRow but i want to keep the scripts in a file and want Talend to pick the .sql file and execute it on SqlServer DB.

[Anonymous]

Hi,
Talend studio is a Java code generator, and your problem is similar with "how to execute sql-script file in Java", please refer to:
http://stackoverflow.com/questions/2071682/how-to-execute-sql-script-file-in-java
In Talend, you can write some Java as suggested in this page in tJavacomponent. Another solution can be to read all contents from the script file to a statement and execute it on tMssqlRow component.
Best regards
Sabrina

[Anonymous]

hi,
perhaps you can try tSystem to exe mysql via command line with something like :

    mysql db_name < script.sql> output.tab

hope it helps
regards
laurent

[Anonymous]

Hi dnahata_infocepts,
From above solutions, is it useful and helpful for you?
Best regards
Sabrina

[Anonymous · Accepted solution]

Hi dnahata_infocepts,
From above solutions, is it useful and helpful for you?
Best regards
Sabrina

Hi,
I was able to achieve this using both the approaches:
1. From command prompt using sqlcmd
2. Reading the Sql File, storing it in a variable and passing the variable to tMSSqlRow
Thanks for your inputs.
Best Regards,
Diwakar

[Anonymous]

Hi,
It is great, thanks for your feedback.
Best regards
Sbrina

[nestoru]

Hi Sabrina,
Sorry to resurrect this thread so feel free to push me to open a new one if you feel so.
Basically I believe Talend is missing an important feature which is to be able to load internal resources. Keeping a sql file related to the project inside the project makes all sense to me. Then able to load that resource could be easily achieved just interacting with the file system once the job is exploded. Talend could even allow classpath:// resource loading meaning providing a directory where custom resources could be added and later on referred from --classpath from the shell/batch script java command invocation.
Does it make sense to you? It would look like a fgeature request.
Thanks,
- Nestor

[Anonymous]

It is possible to do. Please take a look to the component tSQLScriptParser.
I had the same problem and solved it:
http://www.talendforge.org/exchange/index.php?eid=724&product=tos&action=view&nav=2,1,1

[Anonymous]

Hi

Basically I believe Talend is missing an important feature which is to be able to load internal resources. Keeping a sql file related to the project inside the project makes all sense to me. Then able to load that resource could be easily achieved just interacting with the file system once the job is exploded. Talend could even allow classpath:// resource loading meaning providing a directory where custom resources could be added and later on referred from --classpath from the shell/batch script java command invocation.

So far, Talend don't have this feature yet. But you can take a look at a custom component developed and shared by jlolling at Talend Exchange portal, see if it addresses your need. Follows the instructions in this page to install a custom component.
Installing a custom component
Hope it will help you.
Best regards
Sabrina

[nestoru]

Hi jlolling,
Thanks for the component. How can I use it? I see it has an iterate what would be the syntax to run the statement in a tMSSqlRow component for example?
Thanks,
- Nestor