SQL Agent Job Time Limit

1

When executing a SQL Agent Job an error

WARNING: "1" ETL Exception data rows exist in "[PRODStage].[dbo].[EXC_PRODStaging]" dated from "2021-09-15 22:45:00.087" (180 mins). This exceeds the Threshold of "1".

When opened the Package for EXC_PRODStaging there is no evidence to find the Time limit has surpassed.
Since new to this sort of error may know where needs to find this error for Time limit has crossed.

Thanks

2

If this is an SSIS package, it's not really a SQL Agent error. Do you get this warning when running the package manually?

The wording of the message suggest it's a row count issue, not a time limit. I'd interpret it to mean there shouldn't be any exception rows in that table at that point in the package logic. SQL Agent doesn't have a built-in feature to limit execution time, and I don't think SSIS does either (a script task could probably do so however).

3

Thanks for the nearing response.
However myself couldn't find how the THRESHOLD of 1 is alerted. Neither any clue from Agent Job nor when executing the Package.
Also looking inside the package there is no SCRIPT TASK.
Any clue where can invoked.

4

From the wording of the message, it's almost certainly something in the SSIS package. SQL Agent just runs the package, any "alert" or error condition is created by the package itself, unless SQL Agent cannot run it at all.

I didn't mean to suggest that there was a script task in the package, only that the "time limit" you mentioned was not a built-in feature of either SQL Agent or SSIS. Again, I think the error is actually the presence of rows in the EXC_PRODStaging table.

I'm not an SSIS expert, can only suggest:

  1. Run the SSIS package manually (not via SQL Agent) and step through the workflow in debug mode. Pay particular attention to:

  2. Look in the workflow for the EXC_PRODStaging table, check all the input and output flows for that table. Also check for stored procedure names, and then check the code of those stored procedures to see if they access that table. The logic that generates the error may be encapsulated in the procedure and not the SSIS package.

  3. Search the package contents for the text of the error message "ETL Exception data rows exist". Depending on how you edit the package, there should be an option to "view definition" or something similar where you can view the XML of the .DTSX file. If that's not available, you can open the .DTSX in NotePad or NotePad++ and view the XML directly, then use the search feature of the editor. Do not have the package open in 2 programs at the same time, and do not make or save changes in NotePad.

5

Excellent answer and many thanks for your kind effort in deducing deep inside. :ok_hand:

Also, the 3rd option where the threshold was from the "ETL Exception data rows"

exist, is where the amount of time when it crosses the time limit has thrown an alert to caution rest of steps to complete on overall SQL Agent Job to finish.

Your Explanation is exceptional and stands out and once again can't keep calm but to give a thumping applause to you. :clap: