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
47 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
  • 9
  • 5
  • 4
19 Comments
 
LVL 65

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 59

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
 

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 59

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 65

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 59

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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

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 65

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 59

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 65

Expert Comment

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

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now