How to Pass multiple variable values from a Transformation to a Job

[Inder]

Hello all - first time posting here.
I have a transformation that will get a list of zipped files that I want to process in my ETL. I believe that unzipping can only be done in a Job and not a transformation (correct me if I am wrong). So I would like to pass the list of zipped files from my Transformation to a Job. Of course I would have a master job that will have the transformation and job in it.

Thanks,
-Inder

[marabu]

If you can arrange your ZIP files for selection by folders and file expressions, there is a really nice solution for this problem.

[Inder]

Thanks marabu - I learned a few tricks from your code even though the solution didn't work out for my specific case. I found out I was very close to the solution - in my main job I was passing down the "Parameter" name to the unzip Job but had not set the "Stream column name" value from the previous transformation (attached image).pentaho_1.jpg

[raghavavundavalli]

variables used.pngvarible setting.pngabove approach is correct, please observe the below snap shots, first we define variables in table input step ,set those variables in 'set variable stpe' and finally we try to use those variables in our multiple jobs.

thanks

[Inder]

Great tip about setting variables once and using them in multiple jobs. I used "Copy rows to result" in my transformation (as opposed to "Set variables" in your screen shot). The reason being I will typically have many values for my variable "filename" and I want my following jobs to run once for each value of the variable. I couldn't accomplish that using "Set variable". Is there a work around for that? I would love to reuse the value of my variable set in transformation in multiple jobs - is that possible using "Copy rows to result"? I would still need to run the following jobs once for each value of the variable (sort of a loop).
(Btw I got help from very helpful pentaho blog by SLAWOMIR CHODNICKI on how to do this).
-Inder

[joao.ciocca]

hehe Slawo is a jedi master of PDI =D
I deal a lot with jobs and transformations messing around with files, and I found out that depending on what you do, you'll be using a mix of "Execute for every input row" (on the Advanced tab of jobs' properties window) and variable setting...


Here, the "00_Lista_Arquivos" is a simple "Get filenames" -> "Copy rows to result" (no, it's not, I'm just simplifying things) and the "Filtra Arqs Carga" is the job that'll do all the heavy work. For each file listed in a given folder, this subjob will be executed.


This is "Filtra Arqs Carga", after it's recent redesign. At first, it was a whole bunch of "Simple Evaluation" steps, to decide which loading job (be it SQL, Postgre, both or special cases) it would run - until someone here on the forums suggested I'd use variables for the job filename, and moved everything else to a switch/case inside a transformation. So, depending on the filename's mask, the "SetVars Loaders" transformation will set the ${LOADER} variable, which will be used on the "Carga Variável" step filename.

[Inder]

Very cool Joao.ciocca - that's exactly the kind of work around I was hoping for. Makes perfect sense once I see it - I guess I just have to get smarter about how to use combinations of jobs and transformations. Awesome sample! Thanks.

[joao.ciocca]

I'm really glad to help =) I've banged my head against some walls before getting to this stage, and I suck at "wilingly" speak about something... so much easier to answer! =p
(ok, part of the problem is that I'm lazy to de-characterize (sp?) the transformations and jobs, so that nothing from my work's IP leaks out)

but if there's anything else I can help with, just shoot it =)