I'm using the fixed iterator to query a series of tables from source then landing it in Blob storage.
Desired behaviour is to have the filepath name as ${tablename}_${date loaded}. Tablename works dynamically, but my date time doesn't work. I attempted a few options with ${.now()}, ${dt.now()} and a few other options with .format...
Alternatively, I tried to evaluate the date loaded variable with .now() then use ${date loaded} in the iterator, but no luck.
Does anyone have any guidance?
I used the Python component to force this value but not sure if that is the most ideal solution
Hi @DA190190 ,
you can find some hints in the official documentation: https://documentation.matillion.com/docs/2711553
In my project, I have defined an Environment Variable as follows (copy this to your "Project"->"Manage Environment Variables" dialog:
MATILLION_date DateTime Copied Default placeholder for dynamically setting current timestamps. See https://snowflakesupport.matillion.com/customer/portal/articles/2711553-date-and-time-methods- 1900-01-01
You can use this on the Database Query Component as follows (in this example, I use the source name, table name, and load date as file prefix for the staging files):
${v_src_name}_${v_src_table_name}_${MATILLION_date.now().format("yyyy-MM-dd_HH-mm-ss")}_
Let me know if that works for you or if you have any questions.
Thanks Michael. Just so that I understand - you made an environment variable with a default value of 1900-01-01, effectively a placeholder, then you re-evaluate that same variable with the .now() function?
Exactly. The important part is that you can't call the .now() function standalone, but you have to use the variable for this.
Actually, it doesn't matter if it is an Environment variable or a Job variable. Both should work. But with the Environment Variable approach you can define it just once and then use it in all your jobs.
