Avoiding a failed OLEDB connection

[RodBarnes]

[If this has already been answered somewhere, please just direct me to that page. I searched but didn't find anything that seemed to be related.]

Background
When an attempt is made to connect to SQL Server via the OLE DB stage, it will report an error (80004005) if that database cannot be found; i.e., SQL Server is not running, the catalog is not found, etc.

Problem
I have a case where one of my source DBs has been chronically unavailable due to long downtimes so I am getting the above error which causes the export job to abort and then forces the abort of the entire sequence. I want to be able to ignore the abort and just go on if it occurs or, preferrably, run some stage that will check and bypass the export stage if the database is not there.

I tried introducing a validate-only job that could check the connection before attempting the export job but the validate also aborts since it cannot find the database.

Seems the only thing that might work would be to attempt a connection in a command stage (using sqlcmd) and then return a value that would direct flow accordingly.

Any other ideas?

[ray.wurlod]

That's fine, allow the "is the DB there?" job to abort. Have its triggers handle the abort condition, only proceeding with subsequent processing if the database is available. Particularly for this job's Job activity, set "do not checkpoint run" (so that it executed always), and "reset if required, then run".

[RodBarnes]

That's fine, allow the "is the DB there?" job to abort. Have its triggers handle the abort condition, only proceeding with subsequent processing if the database is available. Particularly for this job's Job activity, set "do not checkpoint run" (so that it executed always), and "reset if required, then run".

That's what I think I tried. I have a job that just runs this statement:

SELECT TOP 0 NULL dummy FROM #jpTableName#

using the passed tablename. This job runs from the sequence with the "Execute Action" set to "Validate only". It has an "OK" trigger that goes to the export job and an "Otherwise" trigger that bypasses the export job and lets the sequence continue.

Even though it takes the "Otherwise" trigger (and the export job does not run), the abort from the validate still registers and causes the sequence to abort.

I should also note that the sequence includes an exception handler (rather than using the default) and I have the sequence set to "Automatically handle activities that fail". But I wouldn't think those would come into play since I have the requisite Ok/Otherwise triggers on the validate job.

[RodBarnes]

That's what I think I tried.

I ran it again to confirm. All the jobs succeed ("Finished") except for the validate job ("Validation failed") and the sequence ("Abort").

Looking in the log, I saw this:

Code: Select all

seqExportCompliance..JobControl (@ValidateFocus): Controller problem: Error calling DSRunJob(jobValidateSqlServer), code=-2
[Job is not in the right state (compiled and not running)]

seqExportCompliance..JobControl (@ValidateFocus): Will execute error activity: ExceptionHandler

Which tells me that it couldn't reset the validate job after it failed. So I changed the "Execution activity" to "Reset if required, then run" and then ran it again. But the validate job aborts (of course) and then I see this in the log:

Code: Select all

seqExportCompliance..JobControl (@ValidateFocus): Controller problem: Unhandled abort encountered in job jobValidateSqlServer

Why would it think the abort is not handled when I have both an OK and an Otherwise trigger?

[ray.wurlod]

If you have a Failed trigger this takes precedence over the automatic handling. So that answers your second question. If you want to abort the sequence you can include a Routine activity that invokes the SDK routine UtilityAbortToLog in the stream from this trigger. You could also, if desired, include a Job activity in this stream to reset the first, test, job, or you could just leave it as "reset if required, then run".

[RodBarnes]

If you have a Failed trigger this takes precedence over the automatic handling.

I left the job with the "Execution Activity" set to "Validate Only" and added a stage for the "Otherwise" trigger that resets the job when the validation fails.

I found out that there was a secondary error occurring in the message (DSSendmail) as a result of the failed validation stage. Once I corrected that, it all worked.

Thanks.