Friday, March 23, 2012

Running Time of Package to be logged in the Email Notification

Hi,

I want to include the running time (Start Time and End Time) of the Package in my script task that sends out an email after job completion.

How do I get the start time and end time?

thanks a lot

cherriesh

There is a system variable called StartTime that will give you the start time. For the Endtime; you could use an execute sql task to get the time from the DB (e.g. Select getdate()) and put that value in a SSIS variable.|||You can also use the ContainerStartTime system variable, which should reflect the start time of the current task. The difference between StartTime (which is at the package level) and ContainerStartTime of your SendMail Task will give you the runtime up to that task.|||

is this how i retrieve the system variable?

Dts.Variables("System:Tongue TiedtartTime").Value.ToString

Because I'm having an error:

The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.

cheriesh

|||

Make sure you have either added it to the ReadOnlyVariables property in the Script property pages, or lock it in the script using the VariableDispenser (the better option).

Here's a post that explains why using the VariableDispenser is better:

http://blogs.conchango.com/jamiethomson/archive/2007/08/28/Beware-of-variable-usage-in-script-tasks.aspx

Code Snippet

Dim var As Variables

Dts.VariableDispenser.LockOneForRead("StartTime", var)

MsgBox(var(0).Value)

var.Unlock()

No comments:

Post a Comment