Wednesday, March 21, 2012

SSIS package total Execution time


         
Following code can be used in Script task to get the SSIS total execution time to log in database table.
            DateTime Sdt;
            DateTime Cdt;

            Sdt = Convert.ToDateTime(Dts.Variables["StartDateTime"].Value);
            Cdt = DateTime.Now;

            TimeSpan timeDiff = Cdt.Subtract(Sdt);


            Dts.Variables["RunDuration"].Value = timeDiff.TotalSeconds.ToString("0,0.0") + " S";

Tuesday, March 13, 2012

Souce:http://stackoverflow.com/a/7125367

Scenario:
When an exception is thrown, how can I catch it and then continue execution starting from the line that caused the error?

Solution:


This should not be practiced as infinite loop condition as you may be over looking as flaky system, and not fixing the root cause, but following solution made most sense to me:

You would have to surround any line that could throw an exception in its own try/catch block to accomplish that.
So instead of
try
{
    StatementOne();  // Exception thrown here
    StatementTwo();
}
catch (SOneException) { ... }
You would have to do:
try
{
    StatementOne();
}
catch (SOneException) { ... }
StatementTwo();
If you need to retry an operation due to a (hopefully transient) exception, you can have a method like this:
public static class ExceptionHelper
{
    public static void TryNTimesAndThenThrow(Action statement, int retryCount)
    {
        bool keepTrying = false;
        do
        {
            try
            {
                statement();
                keepTrying = false;
            }
            catch (Exception)
            {
                if (retryCount > 0)
                {
                    keepTrying = true;
                    retryCount--;
                }
                else
                {
                    // If it doesn't work here, assume it's broken and rethrow
                    throw;
                }
            }
        } while (keepTrying)
    }
}
Then you can just write:
ExceptionHelper.TryNTimesAndThenThrow(() => MightThrowATransientException(), 3);
Keep in mind both methods should be used sparingly. The former will clutter your code quite a bit, while the latter could end up taking a lot more time than you think (since its often a better idea to simply alert the user if something unexpected occurs. Thus the emphasis on a transient exception that you really do expect will disappear if you just try again.)