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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Hi Jim,
Thanks for your comment,
I will try this and update with results,
Thank you

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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

ASKER

Hi,
just to let you know that ;

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

ASKER

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

Th job now writes Success to the History Log whether or not a file exists.

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?

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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

ASKER

Yes, I see what you mean Jim...effectively, the job would be running continuously..

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

currently it is scheduled to retry 48 times with a retry interval of 5 minutes...

ASKER

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

"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)?

ASKER

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!! :-)

Thanks for the split. Good luck with your project. -Jim