Multiple SQL Queries Output to File w/ Timestamp in Name. - VisualCron - Forum

Community forum

Librus Prime
2019-11-06T21:00:49Z
How do you output Multiple SQL Tasks (Queries) to 1 file with timestamp in file name?
jrtwynam
2019-11-07T16:58:00Z
Hi,

What I would suggest is creating a job variable to hold the timestamp, using a Set Variable task at the beginning of your job to set the timestamp in that variable, and then you'd have two SQL tasks to pull your results. You could then have two Write File tasks, one for each SQL task to write the SQL task's output to the file, and your destination file would use the value in your timestamp variable.
Joey S
2019-11-11T20:19:49Z
I can address this with the assumption that you want one of two things.

1. One time stamp for the start of the job which will contain one or more tasks that are SQL queries
- Create one job with one task with all of your queries together - see attachment #1)
- the "Output" tab be set how you want/need
- create a second task after the SQL queries that writes a file (Task - File - Write) OR an Email task (if that is what you are looking for)
- the email or file write task can include the time stamp you want (for example - {DATEFORMAT(M/d/yyyy h:mm tt)}) AND the output from your query or queries in the tasks before this file write/email task


2. You want a time stamp for EACH query
- Similar to option 1 above - - Create one job with one task for each of your queries. Be mindful of the Output tab, you will probably want them the same
- Your last task will be like your 2nd task above - a Task that writes a file (Task - File - Write) OR an Email task (if that is what you are looking for)
- the email or file write task will include the time stamp you want (for example - {DATEFORMAT(M/d/yyyy h:mm tt)}) for EACH query - something like this: {TASK(Active|ExecutionTime|hh\:mm\:ss)} AND the output from each of your queries in the tasks before this file write/email task {TASK(Active|StdOut)}

Example for write/email task as described in #2:
{TASK(---your task #1 id here---|ExecutionTime|hh\:mm\:ss)}
{TASK(---your task #1 id here---|StdOut)}

{TASK(---your task #2 id here---|ExecutionTime|hh\:mm\:ss)}
{TASK(---your task #2 id here---|StdOut)}

Etc..
etc..
Joey S
2019-11-11T20:37:00Z
The easiest might be to put the time stamp declaration in the query itself, then you do not have to rely on anything else and your output always has it

Add this to the top of your query (or each one if you need)
SELECT CURRENT_TIMESTAMP

Keep in mind you can have multiple queries in a single SQL Task in VC

Scroll to Top