May 9, 2008 at 8:51 am
Hi,
I am running an SSIS package on a scheduled (daily) basis, and it occasionally
fails due to the source DB not being available (due to backup/restore activity).
Is there a simple way to include up-front some kind of check in the control flow to
ensure that the DB(s) are up and available, and if not, to fail the package?
Thanks in advance
May 9, 2008 at 9:29 am
It is a little tricky because the database connections get checked during package validation unless you set DelayValidation to true.
In the cases in which I have had to do this, I have used a For Loop container with an ExecuteSQL command in it. The command queries the database with "SELECT 1 AS MyCol" but has DelayValidation set to true. The output of the query is assigned to the @gvFilesFound variable. The OnFailure action of the object goes to a script task that waits 5 minutes. The loop container has:
InitExpression:
@gvLoopCount = 0
EvalExpression:
(@gvLoopCount < @gvTimesToSearchForExtract) && (@gvFilesFound==0)
AssignExpression:
@gvLoopCount = @gvLoopCount + 1
So, this will loop @gvTimesToSearchForExtract times and try to run the query and exit the loop when it succeeds.
You then just need to check the @gvFilesFound variable to make sure it is 1 to determine if it connected or timed out.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply