Alternative for checkpoint
-
hi,
I have a requirement in which i have to restart the package where it fails. I initially thought to use check points but that is not possible because i need the solution to be database driven? now I am stuck Need help.
Thanks
Sa.M
-
Sounds like a good way to end up in an infinite loop.
How are you running the package? SQL Agent job? If in SQL Agent, you can create an additional step in the job that will run on failure of the original step. That conditional step could run the package again. That would avoid recursive failure loops, while still allowing a pre-determined number of finite retries.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
yes , i am using SQl agent. The next step will not execute the task from the point in failure right??
-
Right. A new step would start it over again. If it's made any data changes by the time it fails, whether those will exist or not depends on transaction handling in the package.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
No need for an extra step really, if all you want to do it restart that step should it fail. In the job step you can set the Retry attempts and interval.
If you really want the package to pickup exactly where it left off and have that data stored in a database then I think a homemade setup where you wrote your own SSIS Tasks that wrapped every built-in Task you planned to use could work. That would be quite the arduous assignment to complete, test, and inevitably upgrade to the next version of SSIS.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Sam.... (9/25/2012)
hi,I have a requirement in which i have to restart the package where it fails. I initially thought to use check points but that is not possible because i need the solution to be database driven? now I am stuck Need help.
Why? Restarting a package from a particular execution point is the purpose of checkpoints, their sole existance in the construction of the package. The next retry fires from that point.
What is the purpose of forcing the database to control execution pathing within the package? I can see a few ways to do it but it's just huge amounts of work.
Also, be VERY careful with automatic retries, and be prepared to deal with duplicate data issues if you allow the system to retry at whim without direct user intervention as to the problem and understanding of what has and has not fired yet. Your code needs to be very robust to be able to handle automatic retries on anything but the simplest of packages (Trucate staging, datapump flatfile to staging, Run proc to handle i/u/d duties on single table). After that you get into a series of complications that really need to be reviewed. Checkpoints too, but at least you're more granular in your control then.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
I was able to figure out a way..
I need a table which will keep track of success/failure of each task in a package. During next run of the package On the pre-execute event of the package I will check the values of these variables for the day. what I am planning to do is disable the task's which are successful.
Initially i thought i can use script task to modify the Disable property of SSIS task?? IS this possible and how?
But I was not able to find a single article, so the option which i have left is to modify the SSIS variables and use these variables to enable/disable the package. Its a long approach 🙁
any thoughts will be highly appreciated.
Thanks,
Sa.M
-
Think of the overhead you'll be asking every other developer that work not only on this package, but also others that need this same checkpoint-like behavior. It's going to become unmanageable in a hurry!
I would recommend you go back to whoever gave you this requirement and explain to them what is involved in implementing it so they understand what they're actually getting, and all the downsides to implementing this yourself. This is not a trivial thing to 1) implement and more importantly 2) to maintain. Try selling them on the Checkpoint feature built directly into SSIS. You'll be happy you did when it comes time to upgrade SSIS, or when you have more than just a few packages that need this type of feature.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Sam.... (9/25/2012)
Initially i thought i can use script task to modify the Disable property of SSIS task?? IS this possible and how?You can't enable a task during runtime except via variables and then controlling the order of executions via Precedence Constraints, checking for expression alone, Logical and/or combinations for start point control, and then using the OR pathing normally with Constraint SUCCESS for non start point positions. Otherwise once you disable a task it's just... gone for that run.
You're building a Rube Goldberg invention. Why is this requirement in place?
However, some resources I found via a bit of googling with some different keywords:
Locating a particular task: http://www.sqlis.com/post/Searching-for-tasks.aspx
Once you've got your taskhost, taskhost.disable will turn that event off for the entirety of that particular run of the package. It's just about as roundabout as anything else, but it *is* code driven.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
On another note, what are you doing in SSIS that you need SSIS in particular for? If you want to keep this completely database driven, BCP and other tools are available in T-SQL and you can avoid the SSIS altogether if that's what is preferred... and might be a better option here, considering the requirement.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply