Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
SolvedPrivate

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

Posted on 2014-01-24
8
Medium Priority
?
61 Views
Last Modified: 2016-02-10
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
Comment
Question by:blossompark
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 

Author Comment

by:blossompark
ID: 39806700
would event handlers do this?
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1500 total points
ID: 39806719
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
 

Author Comment

by:blossompark
ID: 39806802
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39806886
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39806918
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
 

Author Comment

by:blossompark
ID: 39806968
Hi Kevin,
Re: ForceExecutionResult  value, I have set to success,
0
 

Author Closing Comment

by:blossompark
ID: 39808662
Hi Kevin,
thanks for your input, my requirements for this package  have changed , so I am going to submit a new "Ask"
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39808914
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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question