How to set variables and use it on transformation job

Matillion ETL
1

Hi, all.

​

I would like to know how to set variables and use it on transformation job.

​

As of orchestration job, there is Python Script component, so I can define some variable and give it to next component as a.job variables.

But as of transformation job, there is no component which can define variables like Python Script component.

​

What I would like to do is below.

1. Define '$​start_date' which will be the before 90 days from today, as a string and format is like '20210924'.

I could define like this on orchestration job by Python.

 

2. I would like to use '$​start_date' in SQL component like this.

 

SELECT

*

FROM

test

WHERE

start_date = ${start_date}

 

If there is something component like Python Script on transformation job, or there is some different ways, please let me know.

 

Thank you.

2

I've gotten caught up by this before. There is a solution, I think, but it isn't super-obvious...

 

First, in your Orchestration Job, assign values to some variables (e.g., using Python Script component as you described.)

 

Then, set up a Transformation Job with job variables, to be called by your Orchestration Job.

 

Then, in your Orchestration Job, add a "Run Transformation" component, and look at the Properties of the Run Transformation Component. See "Set Scalar Variables" and "Set Grid Variables" -- these are what you can use to pass the values of your "Orchestration Job variables" to you "Transformation Job variables."

3

Dear Kevin

 

Happy New Year and thank you for your kind advise.​

You mean, on transformation job, I need to define Job variables like 'start_date' first and write it on SQL component like '${start_date}' right??

 

I'm gonna try tomorrow and I'll let you know how it works.

 

Sincerely

 

4

Dear Kevin

 

Hi.

Like I wrote 2days ago, I tried to give some variables to "Run Transformation" component on orchestration job.

As a result, it was successful and this is what I wanted to do!!

 

Thank you for your advise and I hope it will useful for matillion user.

 

Thank you.