Handling SSIS Error Messages
I have an SSIS package that works great. The package works every night and takes about 4 hours. I have a newbie to SSIS so I want to see what my options are. I don't find anything online about these two issues, so any advice is appreciated.
-
What to do when I have an external issue like power failure / accidental restart. Here's a way to alert someone or the package will start again on reboot.
-
A few weeks ago there was a process that hung up and locked tables, making the process fail. As the best way that I have proper access before starting, and if not, get access. I'm fine with killing processes, etc.
Look for information on best practices. Thanks to
a source to share
For # 1 - there is no built-in restart mechanism in SSIS, as there is no built-in "start" mechanism to start with. You will need to look at the process that you control the scheduled execution of your packages, which I suppose might be a SQL agent. Given that your options for detecting the failure of a SQL Agent job and / or restarting that job are the same, whether the contents of the job are SSIS packages or not. There are many stored procedures for monitoring and querying job execution and results. You can also implement your own mechanism for recording the status of a job / batch. SSIS offers "checkpoints" to help you reload packets from specific points, but the general consensus on this feature isthat it is limited in its applicability - your mileage may vary. Personally, I always include a opt-out route in my work to send someone a reject message, and to configure my workspaces and packages to be idempotent so they can be re-run without fear of improperly performing the same operations twice. They either "reset" environments (delete and reboot), or they can pinpoint exactly where they left off.
Item # 2 is a tricky question and depends a lot on your environment and scenario. You can use simple tasks like Execute SQL Task to run "test" commands that are tested to fail if sufficient privileges or locks exist. Or, you can directly ask through SP or other mechanisms to determine if you need to take corrective action before attempting to run the meat of your package. Using priority "on failure" constraints can help with this logic. Thus, event handlers.
a source to share