OrangeScape Developer Community

A community for Application Developers on OrangeScape's Platform-as-a-Service

Query to find output paths stored in kettle transformation and log file path in kettle jobs

If  Kettle jobs are imported from one server to another server and if kettle working directly or output file saving directory in new server is different.

Kettle job may not  run due to path conflict  or new folders/files will be created in the path as existed in earlier server,  which might not be desirable.

If the number of jobs and transformations are huge it will be very difficult to change all those paths manually.

 If you know id_step_type for 'TextfileOutput'  as 2 and id_step_type for 'ExcelOutput'  as 20056 from r_step_type table then use the query below . If the id_step_type is different use appropriate id_step_type in the following query

select t.name,st.name,s.code,s.value_str from R_STEP_ATTRIBUTE s

join r_transformation t on s.id_transformation=t.id_transformation

join r_step st on st.id_step=s.id_step

where s.code ='file_name' and s.id_step in (

select id_step from r_step  where id_step_type in ('2','20056'))


If you dont know id_step_type use the following query to find the output file path information used in different transformations.

select t.name,st.name,s.code,s.value_str from R_STEP_ATTRIBUTE s

join r_transformation t on s.id_transformation=t.id_transformation

join r_step st on st.id_step=s.id_step

join r_step_type sa on st.id_step_type=sa.id_step_type

where s.code ='file_name' and  sa.code in ('TextFileOutput','ExcelOutput')


The following query can be used to find the path information of log files of the kettle jobs

select j.name as 'job name', ja.value_str as 'logfilepath' from r_job j

inner join r_jobentry_attribute ja

on j.id_job=ja.id_job

where code like 'logfile' and ja.value_str is not null

order by j.name


The above queries can be changed and updated as required based on new file/folder paths or log files path information as shown below.

update r_jobentry_attribute set value_str={directory path you want to update}+substring(value_str,2,len(value_str)) from r_job j

inner join r_jobentry_attribute ja

on j.id_job=ja.id_job

where code like 'logfile' and ja.value_str is not null

order by j.name

Views: 404

Comment

You need to be a member of OrangeScape Developer Community to add comments!

Join OrangeScape Developer Community

© 2017   Created by OrangeScape Technologies.   Powered by

Badges  |  Report an Issue  |  Terms of Service