Creating parameters outside the parameters window


I have an easyMorph project for processing files that arrive on a monthly basis. In a database, I store some metadata about these files (eg. the filename, the year/month of the file,…).

I use an iterate transformation to iterate over the files so that finally they can be stored in the database.
In the project with this iterate transformation, there is also a decision built in that checks if the required number of files that have to arrive each month are present. If there are 3 files expected, but if there are only 2 then a mail is sent to the user. If there are enough files, then they are stored in the database.

It could be very handy to add a parameter to the mail saying which files are missing so in order to achieve that I should be able to generate a parameter or some other variable that captures the list of files and inserts it as plain text in the mail.

Does anyone see a possible solution to achieve this ?

Thanks in advance !

I would produce a list of missing files, then use the “Aggregate” transformation in Concatenate mode to concatenate names of missing files into a comma-separated sequence of names. Then used this single value as a parameter to call a subproject (using Iterate in 1-line table) that actually sends the email.

If you’re expecting many files missing, then you can export the names of missing files into a text file, and attach it to the email.

Is there a general way to calculate parameters that are the result of a calculate inside an easyMoprh project ?

Say for example that I have to calculate a YEAR_MONTH using an SQL-query and I want to delete all records in a table before that calculated month. How can I make a parameter from the calculated YEAR_MONTH outside the project parameter window ?

You can assign parameters of a project with table data using the “Iterate” transformation on a table with 1 row.

In your example, produce a table with 1 row and containing the YEAR_MONTH. The use “Iterate” to call another project and assign its parameter with this value.

Thanks Dmitry for the suggestion. Currently I have solved it using a DELTE FROM WHERE statement in which I have put a subquery in the WHERE clause. In that subquery, the YEAR_MONTH is calculated. The whole statement was inserted in a database command transformation.

Maybe I will implement your solution but this requires an additional easuMoprh project file…
I am still not sure which solution gives a more comprehensible ETL-process…