I used a query to find the activity count of the table and if activity count is 0 then I set it to exit out. Is there any better solution t catch the error after the import failed
The .IF command can only test the results of the most recent SQL statement (ERRORCODE, ERRORLEVEL, or ACTIVITYCOUNT), not other BTEQ commands.
You apparently meant to .SET ERRORLEVEL 3807 SEVERITY 0 and then set it back to 8.
There's no way to explicitly set a BTEQ return code based on a missing or empty import file.