SolvedPrivate

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

Posted on 2014-01-25
19
66 Views
Last Modified: 2016-02-10
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
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
  • 9
  • 5
  • 4
19 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 251 total points
ID: 39808860
>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
 

Author Comment

by:blossompark
ID: 39808883
Hi Jim,
Thanks for your comment,
I will  try this and update with results,
Thank you
0
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 249 total points
ID: 39808933
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:blossompark
ID: 39809780
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
 

Author Comment

by:blossompark
ID: 39811565
Hi,
just  to let you know that ;

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

Author Comment

by:blossompark
ID: 39811607
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
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 249 total points
ID: 39811939
Can you program the wait delay (and repeat attempts) in the package, or does this need to be configured from SQL agent job?
0
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 251 total points
ID: 39811985
>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
 
LVL 60

Assisted Solution

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

Author Comment

by:blossompark
ID: 39812014
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
 

Author Comment

by:blossompark
ID: 39812022
Yes, I see what you mean Jim...effectively, the job would be running continuously..
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 251 total points
ID: 39812036
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
 

Author Comment

by:blossompark
ID: 39812041
currently it is scheduled to retry 48 times with a retry interval of 5 minutes...
0
 

Author Comment

by:blossompark
ID: 39812046
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
 
LVL 60

Expert Comment

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

Author Closing Comment

by:blossompark
ID: 39812121
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39812137
Thanks for the split.  Good luck with your project.  -Jim
0
 
LVL 60

Expert Comment

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

623 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