• Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 70
  • Last Modified:

Preventing SQL Server 2008 from logging SSIS package called in a job as failed in the Log File Viewer

Hi,
I have a SQL Server Agent  job that calls an SSIS package.
The SSIS package checks for the existence of a file in a folder.
The file may or may not exist in the folder.
The job retries a number of times  before terminating.
The issue I have is that if the file does not exist , when the job finishes it writes an entry in the History  Log that the “Job Failed”.
The job is scheduled to run every day, but the file will appear only once a month, and which day that happens is unknown.
So I end up with a History Log with job fail entries every day except once a month.

As far as I am concerned , the job is a success if it runs, if no file exists, that is not a failure in this context.

I am experimenting with adding an on failure  precedence constraint,
The File System Task below checks for the file in a folder. If it doesn’t find the file it executes  a meaningless sql query.
package-tasks.jpgI thought this would solve my problem but it hasn’t as you can see from the screen grab below, it still logs a failure in the History Log.
History-Log.jpgAny guidance appreciated.
Thanks
0
blossompark
Asked:
blossompark
  • 4
  • 4
1 Solution
 
blossomparkAuthor Commented:
would event handlers do this?
0
 
Kevin CrossChief Technology OfficerCommented:
Yes.  An empty one is fine.

Here is a nice walkthrough:
http://sqlblog.com/blogs/rushabh_mehta/archive/2008/04/24/gracefully-handing-task-error-in-ssis-package.aspx

- Create OnError event handler for executable File System Task
- Change the value of System::Propagate under File System Task > Event Handlers > OnError > Variables to False

P.S. The author mentions the ForceExecutionResult and ForceExecutionValue in passing, but this is important to making sure the package returns success/completion despite child errors.
0
 
blossomparkAuthor Commented:
Hi Kevin,
Thanks for your input.
I have followed those instructions and am still getting a fail in the History Log.
Here is my event handler configuration
eh
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Kevin CrossChief Technology OfficerCommented:
Did you catch the note on ForceExecutionResult?  What you can do is force the result to completion/success since failure does not really matter.
0
 
Kevin CrossChief Technology OfficerCommented:
P.S. As an alternative solution, you could check file existence via a script that sets a variable, then base the subsequent tasks on expression versus failure/success constraint.
0
 
blossomparkAuthor Commented:
Hi Kevin,
Re: ForceExecutionResult  value, I have set to success,
0
 
blossomparkAuthor Commented:
Hi Kevin,
thanks for your input, my requirements for this package  have changed , so I am going to submit a new "Ask"
0
 
Kevin CrossChief Technology OfficerCommented:
Yes, it seems this works well in some applications but not in others.  It worked for me as I actually tested, but I think the issue is the exact setup.  I would look more into using the Script Task to check the file existence.  You can then do different things in your package based on variables instead of depending on failure and success, which has the negative consequence you have seen.  No matter what, it is normal for a SQL agent job to return completion with errors if there is one error on a task.  Therefore, what you need is for the task to complete successfully with a flag that specifies whether the file exists or not.  Hence, no file is not a failure.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now