January 25, 2014 at 7:23 am
Hi,
I have a SQL Server Agent job that calls an SSIS package.
The first task of the SSIS package is a C# script that checks for the existence of a file in a folder.
The file may or may not exist in the folder.
If the file exists the task completes successfully and the next task in the package is invoked.
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.
So I do not want a job failure written to the History Log of the job.
I still want the job the package to terminate at the first task if a file is not found, but I do not want fail to be written to the History Log of the Job.
Is there some amendment to the C# script I can make to achieve this?
Or is there some other way?
I have checked out the recommendations at these locations below but without success.
January 26, 2014 at 2:15 pm
What is the error you are getting from your package? When coded properly a File Exists in a Script Task shouldn't result into an error.
Maybe you should post the code in your SSIS package as well so we can take a look.
__________________
MS-SQL / SSIS / SSRS junkie
Visit my blog at dba60k.net
January 27, 2014 at 2:41 am
As a quick win, could you not just set the On Failure Action of the job step to be "Quit the Job Reporting Success"?
January 27, 2014 at 3:17 am
Farlzy (1/27/2014)
As a quick win, could you not just set the On Failure Action of the job step to be "Quit the Job Reporting Success"?
That would hide real failures.
@roryoflynn1: it's easy to create a package that doesn't fail when there is no file present. But we'd need to see at least the code in the script task to see what is giving the failure each time.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 27, 2014 at 4:53 am
Hi Peterjonk, farlzy and Koen Verbeech,
thanks for your replies. Just to clarify what I want to achieve
I have set up a test package which has two tasks.
The first task, a C# script (see below), then a precedence constraint before executing the second task which inserts a row in a table.
/*
Microsoft SQL Server Integration Services Script Task
Write scripts using Microsoft Visual C# 2008.
The ScriptMain is the entry point class of the script.
*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
namespace ST_a400c24e782648ea90176536249cd677.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
public void Main()
{
// TODO: Add your code here
String[] DEPOSFiles = Directory.GetFiles(Dts.Variables["User::DirectoryPath"].Value.ToString());
// String Filepath = Dts.Variables["User::DirectoryPath"].Value.ToString() + Dts.Variables["User::FileName"].Value.ToString();
if (
// File.Exists(Filepath))
(DEPOSFiles.Length != 0))
{
Dts.Variables["User::FileExistsFlg"].Value = 1;
}
else
{
Dts.Variables["User::FileExistsFlg"].Value = 0;
}
String FileExistsFlag = Dts.Variables["User::FileExistsFlg"].Value.ToString();
//MessageBox.Show(Filepath);//Show the folder path with file name
//MessageBox.Show(FileExistsFlag);//show the flag value, 1 for exists and 0 for not exists
Dts.TaskResult = (int)ScriptResults.Success;
}
}
}
The precedence constrain checks for a value of
@FileExistsFlg==1
if this is true, it executes the second task,
which writes to the table.
If @FileExistsFlg==1 is not true.
The flow terminates at the end of the first task, and the second task does not execute.
I have added this package to a SQL Server Agent job.
When the job executes, it will now write Success to the History Log, whether or not a file exists.
However, what I need the job to do is;
retry every 5 minutes for an hour, to see if the file has arrived, if the file has not arrived, then I want the job to terminate Successfully, ie write success to the Job Log.
If the file is present, I want the job to also terminate successfully, writing Success to the Job Log.
With my current set up, the retries are not being attempted..
January 27, 2014 at 5:10 am
The set-up of the package is correct.
Retries in a SQL Agent job only work if it actually fails, which is not what you want.
You could create a stored procedure to do the retries.
Start with a WHILE loop. Execute the package. Make sure the package stores the result (file found or not) somewhere in a table.
Retrieve the result: if a file was found, abort the WHILE loop. If it was not found, sleep for 5 minutes (use WAITFOR) and go to the next iteration of the loop. In each iteration, increment a counter. When you have reached 20, stop the loop.
Schedule this stored procedure every day.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 27, 2014 at 7:18 am
Hi Koen,
Thanks for your reply...
I am thinking of coding the looping into the C# script, which is the first task...
The only issue I see with this, is that the dba may object to a package running for 4 hours, the job currently retries 48 times over a four hour period with a retry every 5 minutes...
January 27, 2014 at 7:22 am
roryoflynn1 (1/27/2014)
Hi Koen,Thanks for your reply...
I am thinking of coding the looping into the C# script, which is the first task...
The only issue I see with this, is that the dba may object to a package running for 4 hours, the job currently retries 48 times over a four hour period with a retry every 5 minutes...
The package doesn't run for hours, the stored procedure does in the other hand.
Ask your DBA if he knows how to monitor a file without something continuously looping 😉
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 27, 2014 at 7:42 am
Hi Peterjonk , Farlzy, Koen verbeech
what I've decided to do here is now that I have it writing Success to the Log whether or not a file is present, is to reschedule the job every 5 minutes,
They're will be a lot of "Success" entries in the Log instead of Fails and that was what I was asked to do....
Thanks fo r your comments, it really helps to get these inputs,,,greatly appreciated!! 🙂
January 28, 2014 at 4:21 pm
Hi -
you might want to check out the free "File Watcher" Task @
I have not used it myself but it is used in several of the SSIS packages that I have to support.
January 29, 2014 at 1:31 am
Hi there,
yes I have used konesans in the past and it is a great solution, unfortunately a lot of organisations will not accept it on their systems as the code source is closed and cannot be examined.
January 29, 2014 at 1:47 am
roryoflynn1 (1/29/2014)
Hi there,yes I have used konesans in the past and it is a great solution, unfortunately a lot of organisations will not accept it on their systems as the code source is closed and cannot be examined.
Do these organisations also not install Windows and Office? Those code sources are also closed and cannot be examined.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 31, 2014 at 2:30 am
You could create a new step which is run if the CheckIfFilePresent step fails which will add a new one time schedule entry for 5 minutes time and then end the job.
That way you will only retry when necessary.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply