I have written some automation scripts to import various text files into a database. The script runs everyday. Sometimes the files aren’t in the import directory when the automation script runs. Is there a way to check a return code or something from the Import activity to see whether a file was found or not? I want to branch the process based on whether or not a file was found.
Import Wizard has the following options for the import source (see 2nd page of the wizard):
-
from file (Add File…)
-
from folder (Add Folder…)
-
from query (Add Query…)
-
from SharePoint (SharePoint)
Do I understand correctly that you’re importing from file? If this is the case then you might want to try to import from folder instead. Just make sure only file(s) in need is(are) presented there. In this case no exception is fired - just an empty table is created as an import result.
I hope I answered what you’ve asked. If not - please clarify.
Thanks,
Igor.
Thanks Igor. You do understand my issue correctly. Your approach will not create an exception, which is good. However, after the import step, I’d have to run a query to see if there are new records in the table signifying that a new file was imported. However, I’m just wondering if there is an easier way based off the import activity ‘signaling’ that a file was or was not found.
If there is a specific file name that you are looking for, you can check for the existance of that file prior to the import step and set a variable accordingly and then use that variable to condional branch (import or skip import and send email about non-existant file instead). I have used run program step to pipe the contents of a folder to a text file and then parse the text file to get file names that start with the same begining and then loop through them to process each one that met the match criteria. You could do something similiar and avoid the job failing, complete the remaining steps and get email notifications for which imports failed. Actually you could probably do what you want easier, just try to copy or rename the file using the run program step and a DOS copy or rename command. If the run program step returns 0 continue, else send email. Don't forget to uncheck the Stop on Error box on the Activitty tab:
Thanks Greg. That’s a great solution for me. I actually have a copy step before the import. I didn’t know that the run program activity would return a non-zero code if there wasn’t a file. I’ll have to test that, but it seems like it would be an even better solution than what I was trying to do.
I ran a quick test in version 3.7 and it brings back the return code of 1 instead of 0 if the file is not found but it did not continue even though I unchecked the box that says stop on error. Let me know if you see the same thing and what version you are using.
Greg, it’s a great idea to collect file names of a particular folder in a text file. Are you using a connection to that text file to parse its contents with any of the three database activities?
Btw, using Run activity for running some batch file returning non-zero exit code seems simpler. But unfortunately any non-zero exit code is interpreted as an error code even if it’s logically not. We are thinking about changing this behavior but nothing has been decided yet.
Igor.
Doesn’t work for me either running 3.7. As Igor points out, the non-zero return code from a batch file causes the flow to error out. Will need to look into your suggestion of checking if the file exists prior to import.
I did some further testing. This used to work in version 3.2 but got broken somewhere after that, at least by 3.6 (I don’t have any versions in between those two). I am attaching the 3.2 log file where you can see it logged a comment using the retrun code variable and did a send email step after failing the batch file copy Run Program step. In 3.6 and 3.7 the log file shows the return code set to 1 but the job stops and does not do the log comment or send email steps. If I give it a valid file name to copy it does log the comment and send the email.
Igor or Debbie,
This is a bug introduced after 3.2 on or before version 3.6
TestFileCopyError32.log (1.44 KB)
Igor,
Sorry saw your email after. I used to actually import the text file into a SQL Server table, skipping the 1st x number of rows and then grabbing the file names starting at position 40 (If I remember correctly). I’m willing to bet using the text file as a data connection is easier, just did not think of that way back when I built this (probably on version 2.7).
When you uncheck that box for stop on error, it should not stop. This is a bug. One can no longer trap errors and conditionally process on that error code returned.
I will look into it. But in the meantime, use ‘Continue on Error’ button in the Settings activity rather than unselecting Stop on Error checkbox for the single particular activity. The ‘Continue on Error’ button changes the behavior in question for the whole script.
Igor.
Tried that button too, did not work. Better to be able to fail on any step but override only the steps you want to trap the error on, like how it used to work in version 3.2. Not sure why a more recent version would break this or design a change like that. That is a loss of functionality to have to trap all errors or none.
Weird. Just checked with the current 3.8 and 3.7.1 release builds - works for me. See below:
– 9/9/2015 5:55:03 PM: Script_1_37.log: Setting up environmen
– 9/9/2015 5:55:03 PM: Script_1_37.log: Script run by Toad Data Point 3.7.1.1011 as imanokhi on DSG4Y65P32
– 9/9/2015 5:55:03 PM: Script_1_37.log: Build started
– 9/9/2015 5:55:03 PM: Script_1_37.log: Compiling script
– 9/9/2015 5:55:03 PM: Script_1_37.log: Start CompileWorkflow
– 9/9/2015 5:55:03 PM: Script_1_37.log: Before Compile
– 9/9/2015 5:55:04 PM: Script_1_37.log: Return from CompileWorkflow
– 9/9/2015 5:55:04 PM: Script_1_37.log: Workflow Compiled without Errors
– 9/9/2015 5:55:04 PM: Script_1_37.log: Workflow Compiled has warnings
– 9/9/2015 5:55:04 PM: Script_1_37.log: Warning: No Exception Handler. To send an Email when there is an error, change the value of Email on Error to true and enter email values.
– 9/9/2015 5:55:04 PM: Script_1_37.log: Build completed
– 9/9/2015 5:55:04 PM: Script_1_37.log: Create Workflow instance
– 9/9/2015 5:55:04 PM: Script_1_37.log: Begin execution script activities
– 9/9/2015 5:55:04 PM: Script_1_37.log: Started workflow instance
– 9/9/2015 5:55:04 PM: Script_1_37.log: Begin Export of File_1
– 9/9/2015 5:55:04 PM: Script_1_37.log: Connection description = SMOKETEST_ORACLE11 (QUEST_DEV), QUEST_DEV
– 9/9/2015 5:55:04 PM: Script_1_37.log: Connection node found
– 9/9/2015 5:55:04 PM: Script_1_37.log: Connection to: Oracle_DailyTest
– 9/9/2015 5:55:04 PM: Script_1_37.log: Begin Export
– 9/9/2015 5:55:04 PM: Script_1_37.log: SQL to execute: select * from REGIONS
– 9/9/2015 5:55:04 PM: Script_1_37.log: Beginning export
– 9/9/2015 5:55:05 PM: Script_1_37.log: File_1 - ORA-00942: table or view does not exist
– 9/9/2015 5:55:05 PM: Script_1_37.log: Continuing after error
– 9/9/2015 5:55:05 PM: Script_1_37.log: Some comment
– 9/9/2015 5:55:05 PM: Script_1_37.log: Completed with errors
I generated an error in Select to File activity and then script continued an execution with Log Comment activity (‘Some comment’ entry). What is your use case exactly? Can you send me (igor.manokhin@quest.com) your failing script to take a look? I can take a look at it tomorrow morning. It might be I don’t understand completely what use case you’re describing.
Thanks,
Igor.
The Run Program step does not allow the Toad Automation to go to the next step when there is a failrure even if you tell it to continue via the uncheck stop on error or set the entire job to continue on error. Try your test again with a Run Program step in it. Try copying a file that does not exist with a simple batch job.
Hi Greg,
You’re right. I found my old script where I modeled this exact scenario for another customer. Ran it and saw that something is definitely wrong :(. I’ll come up with a QAT number a bit later.
Igor.
Greg,
Okay, I fixed it. See the restored functionality in the next beta. QAT-6400.
And thank you for pointing this out.
Igor.
I just want to say this application has the most awsome support ever! Can’t wait for 3.8.? with that QAT-6400 in it. Thanks,
Okay, we finally introduced custom exit codes for the Run activity. Codes 1 - 16383 (hex 1 - 3FFF) are still error codes and Run activity will continue to produce errors in the script for the exit codes from this interval. Codes starting 16384 (hex 4000) are now custom exit codes. Now users are free to use them for analyzing results of the custom batch files run by the Run activity.
See the fix in the next beta.
Igor.