We have developed an automated report with TDP that sends a commission summary out to the field. It goes out to several thousand people daily. Presently we have to use a loop of the recipients and for each recipient a subsequent query must be run to capture additional information as part of an individual select to file which is then appended with a header and footer and emailed.
The problem with this approach is that it pushes the spool capacity on the ID it's running under requiring several thousand queries to be run. What we're trying to do is consolidate their summary details into the loop, but have not confirm whether TDP can write to a file using only the output variables rather than having to run a separate select on each user.
Is it possible to write to file from within a loop without using a select statement?
Thanks for sharing the high-level aim of your work flow. Quick answer is that there are several ways to write to a file without using SELECT. Not knowing the details of your workflow logic, or where you're getting the "output variables", have you considered the following alternatives?
-
Sounds like you're using the "Select to File" task. Instead of running this task against the database for each user in your loop, Run a Select to File task before the loop to get all the info for all users with one SQL (query or script). Then, loop through that output file (off-database) to create your user-specific output. You can still use "Select to File" within the loop, but you'll be "SELECT"ing on the file, not on the database.
-
Alternatively, you can use the "Run a Program" task to run Windows/DOS commands, powershell scripts, or launch other applications from Windows. I just now created a simple Automation job that loops through a CSV file, and then ECHOs each of the fields to write to another output file. No SQL, other than the "SELECT" from the CSV file.
-
Run one SELECT to gather all info, like in bullet one above, but push the results to Local Storage or Toad Intelligence Central... then use that off-loaded table in your loop.
Hope one of these alternatives give you some ideas.
The loop is query-driven. Then inside the loop it extracts the user's details, runs a powershell script to handle the file concatenation into HTML, and then sends the email. I considered writing the users to file and doing all the operations on it through python or powershell, but it requires more error-handling to an already working process.
I have not tried querying a file, but that sounds like what I need to do. I would write the user data to it (excel?) and then use it as a data source. Can it be cvs or does it have to Excel?
I'm not clear on the process to set that up as I don't see a way to create a file as a source. Can you provide an example? Thanks.
Do you have any examples as to how to query a file or a local table?
It looks like the way to do this is create a connection to a file, but I'm wondering if that will work on a null file.
Yes, correct... create a connection to a file... here's a snap that shows the properties of connecting to an example CSV file. Note that a truly "null" file (one that is completely empty) won't work. This is due to Microsoft's driver when reading the file. However, everyone I know reserves the first line of their text file to define the column names, anyway, and you'll no doubt want to do that also, so you should be good. Note also in the snap that the text file has no data (almost null), other than the column headings.
Once TDP is connected, you'll be able to create queries via either the Visual Query Builder, or the Editor. Use SQL just like you would on a database... e.g. in this example, the following query brings back all the data. You'll also be able to reference such queries in your automation scripts.
SELECT e-mail, empnum
FROM `writetofile.csv`;
Perfect, thanks for the assistance Gary.
