Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

How to pass variable in SNS component Message property

Hi,

I am new to Matillion and developing the job which requires job fails and success email.
I need to pass job name, current_timestamp using variable of success and failures using Subject/Message property of SNS component.

Could you guide me on this?

Thanks,
Amit

12 Community Answers

Matillion Agent  

Laura Malins —

Hi Amit

In Matillion you can reference any variables at any point using the ${} syntax. So in the SNS message property you can use a variable by referencing ${my_var_name}

The job name is available using a built in variable called ${job_name}. The current timestamp can be determined using our date time methods:
https://redshiftsupport.matillion.com/customer/portal/articles/2711553?b_id=8915

You might also want to explore the export tab of an Orchestration or Transformation sub job as this will allow you to export the message, duration, row count etc from a job as a variable which you can then pass into the SNS component.

Thanks
Laura


Amit Sonavane —

Hi Laura,

Thank you for your response. I am able to print job name using ${job_name}. I am still struggling to print current timestamp value using ${dt.now()} in the Subject property of SNS. Let me explain what i did so far.
I wanted to send email notification of failure and success using SNS component.
For that I have defined environment variable current_timestamp with Type as Text, Behavior as Copied and Variable Value as {dt.now()}.
And I am calling this variable in Subject property of SNS like -
CDW_ODS_Alert_${ENV} Incremental load for ${job_name} failed at ${current_timestamp}

When I run the job i receive email notification in following format -
CDW_ODS_Alert_DEV Incremental load for pw_stg_leads failed at ${dt.now()}

Am I doing something wrong here? Could you guide me on this?

Also, it would be really helpful if you send some example that shows how to print no. of rows inserted and no. of rows merged values in the Message property of SNS.

Thanks,
Amit Sonavane


Matillion Agent  

Laura Malins —

Hi Amit

For the ${dt.now()} your variable needs to be a date time type and in the support article dt is the variable name so if your variable is called current_timestamp you can reference now as ${current_timestamp.now()}. You should also give the current_timestamp variable a sensible default.

For the row counts you can export a row count from the sub job to a variable

Thanks
Laura


Amit Sonavane —

Hi Laura,

This worked. Thanks a ton for your response.
Could you share some example of row count? As I am new to Matillion and still trying to get hang of it.
Appreciate your help...

Thanks,
Amit


Amit Sonavane —

Hi Laura,

Basically, I am looking for row count of inserted rows and updated rows in my transformation job and pass this row count in orchestration job.
I hope to get reply soon.

Thanks,
Amit


Matillion Agent  

David Lipowitz —

Hi Amit,

Please see the following article on Populating an Audit Table

In there it discusses how to populate variables with row counts derived from Transformation components. The article goes on to describe how to insert those values into an audit table. I’m unsure if that’s your use case or not, but either way the beginning of the article discusses the topic you’re interested in, namely assigning row counts from transformation operations to variables in Matillion.

Hope that helps and please let us know if you have any more questions.

Best Regards,
Dave


Amit Sonavane —

Hi David,

I was trying to achieve row count from source. I have PostgreSQL as source and for that I am using Database Query component and I want to kill the process of transformation job when source has 0 records for particular time.
In the export tab of Database Query I have set Source variable as Row Count and Target Variable as Audit_rowcount which is set in Environment variable with default value of -1.
Now, when I use If component with condition like Audit_rowcount is equal to 0 then send notification saying source has 0 records and stop rest of the process. But looks like this is not working at all.
After careful i found that Datbase Query component does not support Variable Export of Row Count and hence i am not able to achieve what I am expected.
Do you know any alternative way? Please let me know.

Thanks,
Amit


Matillion Agent  

Craig Rouse —

Hi Amit,
The database query does support exporting the row count to a variable.
Maybe you could send us an export of your job and we can see what is going on.
Thanks,
Craig


Amit Sonavane —

Hi Craig,

Could you share your email address so I can share the job?
Let me know.

Thanks,
Amit


Matillion Agent  

Craig Rouse —

Hi Amit,
Could you open a support ticket by sending it to support@matillion.com?
Then it will go to just Matillion, and into a queue served by several Solution Architects.
Thanks!
Craig


Kieran O'Flynn —

Hi,

Is there a way to parse out a grid variable in a SNS component message?

Or otherwise how would I go about getting 1 or more rows from a table into an SNS message?

Thanks,
Kieran


Matillion Agent  

Laura Malins —

Hi Kieran

Currently there’s no way to directly reference the grid variable in the SNS component. However a workaround would be to use python to write the results of a grid variable into a job variable and then you can reference the job variable in the component. This will appear as a string containing arrays in the message.

Thanks
Laura

Post Your Community Answer

To add an answer please login