Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 72
  • Last Modified:

Forcing outcome of “ failed” SSIS package to log as “Succcess” in SQL SERVER 2008 Job Log File Viewer

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.
ee-ssis-control-flow.jpgThe 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.
I have included the c# script.
Is there some amendment to this 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.
http://sqlblog.com/blogs/rushabh_mehta/archive/2008/04/24/gracefully-handing-task-error-in-ssis-package.aspx
http://dougbert.com/blog/post/Faking-success-in-SSIS.aspx

Any guidance appreciated
detect-file-presense-script.txt
0
blossompark
Asked:
blossompark
  • 9
  • 5
  • 4
6 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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 a success if it runs, if no file exists, that is not a failure in this context.

One way to pull this off is:

1.

Create a bFileFound variable, and in your script task set it to True if found, False if not found.

2.

Edit the green Precedence Constraint arrow from the script task down,
      Expression and Constraint
      Success
      @bFileFound == True
      Logical OR

3.

Then create a 'dummy' container, and name it something like 'There is no file, so the package exits here.'

4.

Connect the script task to this new container, with a precedence constraint that goes like this
      Expression and Constraint
      Success
      @bFileFound == False
      Logical OR
0
 
blossomparkAuthor Commented:
Hi Jim,
Thanks for your comment,
I will  try this and update with results,
Thank you
0
 
Kevin CrossChief Technology OfficerCommented:
This is what I was suggesting in your other question, but Jim laid this out very clear.  Therefore, I will let Jim follow this one through, but I will point out that you want to set the variable instead of throwing exceptions.

The exceptions create the failure status, and the key here to maintain success but use expressions to control the flow of the package versus the status.

Good luck!
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
blossomparkAuthor Commented:
Hi Jim Horn and Kevin Cross,
Thanks for your comments.
I have run into  issues with  creating the environment to test  these ideas, the  issue is with my very simple C# script.
I have posted a new question regarding this named;
Simple C# script in SQL Server 2008 SSIS Package not writing to Variable

I would appreciate if  you could have a look at this,,,
Thank you
0
 
blossomparkAuthor Commented:
Hi,
just  to let you know that ;

Simple C# script in SQL Server 2008 SSIS Package not writing to Variable
is now resolved,
0
 
blossomparkAuthor Commented:
Hi,

I have created a variable in the script, if the file is found  a 1 is written to the variable, if not  a 0 is written.
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;  
        }
          
        }
    }

Open in new window

I have  created an expression and  precedence constraint
precedence
Th job now writes Success to the History Log  whether or not a file exists.
job history
But  what I want is for the job to continue to retry to see if a file exists, and only terminate when the number of retries  have been attempted.
With the current situation, because  I guess, it reads  success every time, it is not attempting retries...
Any ideas on how I can incorporate the retries  and then write Success to the Log even if no file is found with all the retries?
job prop
0
 
Kevin CrossChief Technology OfficerCommented:
Can you program the wait delay (and repeat attempts) in the package, or does this need to be configured from SQL agent job?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>But  what I want is for the job to continue to retry to see if a file exists
Most people I know that are in charge of scheduling SSIS jobs are going to object to having an SSIS in an 'wait and re-execute every x minutes' loop, as it takes up processing time, and makes it hard to tell if the package is executing correctly, or spinning.

Better to handle it in the SQL Agent job.
0
 
Kevin CrossChief Technology OfficerCommented:
Good point, Jim!  The dilemma is the retry depends on failure, right?  An alternative is to include the SSIS package execution multiple times in the job.  If the wait between retries is significant in allowing the success of the file creation, you can use WAITFOR DELAY steps in between.  Otherwise, you may have to live with the failure message on the package to have SQL retry on failures, but you the failure action of the job step report success.  I have not tried that and retry at the same time, but may work.  I will defer to Jim on that.
0
 
blossomparkAuthor Commented:
Hi Kevin,
Yes,that is an option,
I am currently trying to code it into the c# script,
the other area i will look into is a for each loop container
0
 
blossomparkAuthor Commented:
Yes, I see what you mean Jim...effectively, the job would be running continuously..
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Let's back up a step here.  Spell out for us the timing of these files.  Are they created only at set time(s), or anytime?

Reason I ask is because it's typical for schedulers to execute a job every hour / day / 15 minutes / 1 minute / whatever, and 'sweep' whatever folder it points to, for any files that are there.

If no files, it executes and returns success, no big deal.
If files, it executes and processes the files.
0
 
blossomparkAuthor Commented:
currently it is scheduled to retry 48 times with a retry interval of 5 minutes...
0
 
blossomparkAuthor Commented:
re the files....i am told it can land anytime , once a month, monday to friday inclusive,,,,the job to check for this file kicks off at 2am
0
 
Kevin CrossChief Technology OfficerCommented:
"48 times with a retry interval of 5 minutes" is a minimum of 4 hours, which seems a long time to repeat.  What is the interval between the job schedules?  How long does the job take to run if the file is found (i.e., finish the other processing)?
0
 
blossomparkAuthor Commented:
Hi Jim and Kevin, thanks for all your help on this... 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,  as Jim says,
If no files, it executes and returns success, no big deal.
If files, it executes and processes the files.

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 a million guys....I learnt a lot on this with your assistance,,,,greatly appreciated!! :-)
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the split.  Good luck with your project.  -Jim
0
 
Kevin CrossChief Technology OfficerCommented:
I thought Jim deserved all the credit here, but I too appreciate the split.  I enjoy dialog with folks like you and him as I learn different business uses of SQL tools as well as different perspectives on solutions as tend to think more like a developer than an administrator.  Often there are ways to program around anything, but with the correct architecture and sufficient programming it may be unnecessary to find solutions to everything as you found here.

Good luck!

Best regards and happy coding,

Kevin
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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