Hello SSC! Happy New Year and I hope everyone had a safe and healthy holiday!
I have this ETL process that executes over 50 stored procedures and several data flow tasks. The error handling in the ETL itself is decent, but if a stored procedure fails, no errors are thrown.
My idea was to create a proc at the end of this process that gives me a list of stored procedures that executed within a 24 hour period and capture the errors. I have no idea where to start. I can add try-catch functionality at the end of every proc, but that is not efficient or realistic. This needs to be done correctly the first time, so I ask SSC! 🙂
Any help would be greatly appreciated.
Dave
The are no problems, only solutions. --John Lennon
January 11, 2024 at 2:57 pm
By default, stored procedure failures would (as far as I remember) fail the package from which they are executed. I assume you've configured things so that this does not happen? Would you like to change that behaviour?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 11, 2024 at 3:02 pm
Hi Phil,
Nice to see you and Happy New Year!
You are correct. The ETL is set up to continue on failure due to the amount of data that is being processed. 10 hours is the average run time.
Thank you
The are no problems, only solutions. --John Lennon
January 11, 2024 at 3:45 pm
Thank you!
Can you give an example of the sort of failures you are referring to? I'm assuming nothing terminal (network failure, hardware failure etc).
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 11, 2024 at 3:47 pm
Options are the Try-Catch block at the end of the procs. Although this is really inefficient, it follows the original design of the ETL job. I think consistency is important in this case. I can also add a proc at the end of the process that checks SP's for failure.
Your thoughts?
The are no problems, only solutions. --John Lennon
As these 'errors' are not considered significant enough to halt execution, I'd suggest logging them as they happen to an ErrorLog table and picking up / reporting any entries created during the 'current' run at the end of the ETL process.
This does, of course, require you to go down the TRY/CATCH avenue, but it should be boilerplate code.
You mention adding a 'Try-Catch block at the end of the procs' – I assume you do not mean exactly that. Only the CATCH block will be at the end, with the TRY part surrounding the bit that can go wrong.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 11, 2024 at 5:21 pm
If the ETL is set to continue on failure, it should still log errors to the SSIS catalog (if you're using it) so they should be possible to query.
I would probably fix the procedures, but it is also possible to enable on-error logging without failing the package.
The link below describes how to do it inside a for loop, but it works anywhere. If you have 50+ tasks it will be a lot of work, but you might also be able to put groups of procedures in sequence containers and apply the technique to the container.
The description below involves an empty on-error event handler, but you could put the logging in the event handler rather than doing it for each task. The event handler is required to allow setting the system variables to not propagate errors.
January 18, 2024 at 6:21 pm
Thank you very much for your responses.
Would you happen to know which system variable shows which proc executed? That is the main issue.
The are no problems, only solutions. --John Lennon
January 18, 2024 at 6:36 pm
Thank you very much for your responses.
Would you happen to know which system variable shows which proc executed? That is the main issue.
If you mean within the proc itself, I think this does it:
OBJECT_NAME(@@PROCID)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 18, 2024 at 8:48 pm
Hi team - I found it. Basically you use the SSIS variables as Ed mentioned. We are using the SSIS variables, but some bonehead aliased them to Value_1 and Value_2. Error and error description. Hard to find in a big table.
Thank you both very much for the time. I hope you have a great new year!
The are no problems, only solutions. --John Lennon
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply