Hi,
I have 2 apps in automation designer, these app will export the dataset in my local PC.
I want to save the file using current date. I have use %DATEFILE% format but the outcome from this format is : 2021_7_9(yyyy_mm_dd).
I want the format to be like this: 20210709 (yyyymmdd).
How can i change the format for %DATEFILE%?
Thanks
One way to do that would be using a query iterator to create your own date variable. In the screenshot below, I dropped a query iterator into automation designer, renamed it to QI, and entered a query to return my variable value:
Then, drop the export dataset action off of that (notice it is a sub-node in the tree, not just a node after the query iterator. This is important). And you'll see that my variable is %QI.DATEFILE%
Then, when you want to run the export, run the Query Iterator action, not the export dataset action.
Hi John,
Thank you for your reply. I have tried using the SQL Iterator, but i found difficult to run the export dataset from sql iterator. I need to double click from SQL Iterator and click run using connection and then it is able to export my desire naming file.
My task scheduler also not function when i set from Query Iterator action.
Please advise the solution for my automation to run with expected result.
THanks
Does this work? Rt-click an query iterator, then Run?
If not, make sure that query iterator and export dataset are assigned to the same connection.
Double-click each, then look on the status bar of the properties dialog for each. It sounds like maybe they are different.
Once that is right....schedule the Query Iterator action. Not the Export Dataset action. The Query Iterator will run the Export Dataset.
Hai John,
I have check, on the step 2. When I double click from export dataset. The file display as below
My both query iterator & export dataset action are both in same connection. From schedule, I also have set from Query Iterator but somehow my schedule
Is not running.
It looks like we stumbled across a bug. Using a system variable name (see options -> variables) in a query iterator doesn't work when scheduled. Weird.
Do this:
In your query iterator, change the field name to something else (I changed it from DATEFILE to THE_DATE)
Then, in your export dataset action, change your filename accordingly... so, c:\%QI.THE_DATE%.csv or whatever.
Scheduling of the SPJ_TIPPERTRUCK_QI app should work after that.
-John
Hello,
Not sure if this query is still valid. Tried above hack and filename still uses variable name as %xxx% so seems the bug using variable is still an issue. Wanted to save a report with date stamp to it.
Any help much appreciated. Ta
Hi @r.kayastha
It's kind of a long thread above so I don't know exactly what you've tried and what happened.
Please let me know which Toad version you have and post a screen shot of what you've tried and what the result is.
Thanks
Hi John,
Thanks for speedy response. New to this forum and TOAD automation designer. Using TOAD 16.2 (sorry should have mentioned that in my last email!)
My steps are:
My automation run fine but saves the file as %Q1.filename so all runs fine but unable to use value set in variable filename. I even tried different variable names but same result.
Thanks!
Rachna
Hi Rachna,
When you drop a query iterator on the Automation Designer, it's default name is "Query Iterator1". If you don't rename it, then your variable name would have to be %Query Iterator1.Filename%.
I usually rename the the Query Iterator actions to just QI (the second letter is the letter I, not a one).
You said "Q1.filename". Did you really name it Q1? If not, that's your problem.
Hello,
I did use the default at first but filename got exported as %Query Iterator1.filename% so tried renaming it but no success. I could user filename%sysdate% but need info on how to convert sysdate from dd/mm/yyyy to a text.
Thanks
Hello figured it out using TOAD global variable in the end. I used %DATEFILE% in my output filename and got the date in my filename!
Will have to dig some more on using user variable so your help will be much appreciated.
Thanks again for your reply and help.
Rachna
Was your Export Dataset action nested under the Query Iterator, or just after it?
It needs to be nested.
To nest it, drag the export action on top of the query iterator action.
Ah I see! Will try it this way as Export was not nested under query iterator, makes sense now,
Thanks!
Just to say it worked!
Do you want to use variables to decide which script to run or where to direct output? I see you have tried both.
I have looked at the code for Toad version 16.3. Both should work. From what I can see, it looks like you set it up right. What happens when you try to run it?
Thanks for the quick reply. When I run it, it looks like this:
The task is to run a script on different databases and store the result in a file under the database name.
I found my mistake. You are not allowed to add the variables to the file list in execution script control.
Another question...Can I attach the output to a file as a comment?
