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:tartTime").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()

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:tartTime").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()

running sql2005 job

hi, I have a job failed for the following error. The job is send an email to
me(outlook). any ideas?
Thanks
Executed as user: GOEVO\SRVAdmin. SQL Server blocked access to procedure
'sys.sp_OACreate' of component 'Ole Automation Procedures' because this
component is turned off as part of the security configuration for this
server. A system administrator can enable the use of 'Ole Automation
Procedures' by using sp_configure. For more information about enabling 'Ole
Automation Procedures', see "Surface Area Configuration" in SQL Server Books
Online. [SQLSTATE 42000] (Error 15281). The step failed.mecn,
Yes, the idea is to do what the messages says: "enable the use of 'Ole
Automation
Procedures' by using sp_configure."
sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE WITH OVERRIDE
GO
Or use the Surface Area Configuration tool to do the same thing
interactively.
RLF
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uxeNiReqHHA.4224@.TK2MSFTNGP02.phx.gbl...
> hi, I have a job failed for the following error. The job is send an email
> to me(outlook). any ideas?
> Thanks
> Executed as user: GOEVO\SRVAdmin. SQL Server blocked access to procedure
> 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this
> component is turned off as part of the security configuration for this
> server. A system administrator can enable the use of 'Ole Automation
> Procedures' by using sp_configure. For more information about enabling
> 'Ole Automation Procedures', see "Surface Area Configuration" in SQL
> Server Books Online. [SQLSTATE 42000] (Error 15281). The step failed.
>|||Thanks a lot. I apprecite
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23eAfSXeqHHA.4548@.TK2MSFTNGP03.phx.gbl...
> mecn,
> Yes, the idea is to do what the messages says: "enable the use of 'Ole
> Automation
> Procedures' by using sp_configure."
> sp_configure 'Ole Automation Procedures', 1
> GO
> RECONFIGURE WITH OVERRIDE
> GO
> Or use the Surface Area Configuration tool to do the same thing
> interactively.
> RLF
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uxeNiReqHHA.4224@.TK2MSFTNGP02.phx.gbl...
>

running sql2005 job

hi, I have a job failed for the following error. The job is send an email to
me(outlook). any ideas?
Thanks
Executed as user: GOEVO\SRVAdmin. SQL Server blocked access to procedure
'sys.sp_OACreate' of component 'Ole Automation Procedures' because this
component is turned off as part of the security configuration for this
server. A system administrator can enable the use of 'Ole Automation
Procedures' by using sp_configure. For more information about enabling 'Ole
Automation Procedures', see "Surface Area Configuration" in SQL Server Books
Online. [SQLSTATE 42000] (Error 15281). The step failed.
mecn,
Yes, the idea is to do what the messages says: "enable the use of 'Ole
Automation
Procedures' by using sp_configure."
sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE WITH OVERRIDE
GO
Or use the Surface Area Configuration tool to do the same thing
interactively.
RLF
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uxeNiReqHHA.4224@.TK2MSFTNGP02.phx.gbl...
> hi, I have a job failed for the following error. The job is send an email
> to me(outlook). any ideas?
> Thanks
> Executed as user: GOEVO\SRVAdmin. SQL Server blocked access to procedure
> 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this
> component is turned off as part of the security configuration for this
> server. A system administrator can enable the use of 'Ole Automation
> Procedures' by using sp_configure. For more information about enabling
> 'Ole Automation Procedures', see "Surface Area Configuration" in SQL
> Server Books Online. [SQLSTATE 42000] (Error 15281). The step failed.
>
|||Thanks a lot. I apprecite
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23eAfSXeqHHA.4548@.TK2MSFTNGP03.phx.gbl...
> mecn,
> Yes, the idea is to do what the messages says: "enable the use of 'Ole
> Automation
> Procedures' by using sp_configure."
> sp_configure 'Ole Automation Procedures', 1
> GO
> RECONFIGURE WITH OVERRIDE
> GO
> Or use the Surface Area Configuration tool to do the same thing
> interactively.
> RLF
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uxeNiReqHHA.4224@.TK2MSFTNGP02.phx.gbl...
>

running sql2005 job

hi, I have a job failed for the following error. The job is send an email to
me(outlook). any ideas?
Thanks
Executed as user: GOEVO\SRVAdmin. SQL Server blocked access to procedure
'sys.sp_OACreate' of component 'Ole Automation Procedures' because this
component is turned off as part of the security configuration for this
server. A system administrator can enable the use of 'Ole Automation
Procedures' by using sp_configure. For more information about enabling 'Ole
Automation Procedures', see "Surface Area Configuration" in SQL Server Books
Online. [SQLSTATE 42000] (Error 15281). The step failed.Thanks a lot. I apprecite
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23eAfSXeqHHA.4548@.TK2MSFTNGP03.phx.gbl...
> mecn,
> Yes, the idea is to do what the messages says: "enable the use of 'Ole
> Automation
> Procedures' by using sp_configure."
> sp_configure 'Ole Automation Procedures', 1
> GO
> RECONFIGURE WITH OVERRIDE
> GO
> Or use the Surface Area Configuration tool to do the same thing
> interactively.
> RLF
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uxeNiReqHHA.4224@.TK2MSFTNGP02.phx.gbl...
>> hi, I have a job failed for the following error. The job is send an email
>> to me(outlook). any ideas?
>> Thanks
>> Executed as user: GOEVO\SRVAdmin. SQL Server blocked access to procedure
>> 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this
>> component is turned off as part of the security configuration for this
>> server. A system administrator can enable the use of 'Ole Automation
>> Procedures' by using sp_configure. For more information about enabling
>> 'Ole Automation Procedures', see "Surface Area Configuration" in SQL
>> Server Books Online. [SQLSTATE 42000] (Error 15281). The step failed.
>|||mecn,
Yes, the idea is to do what the messages says: "enable the use of 'Ole
Automation
Procedures' by using sp_configure."
sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE WITH OVERRIDE
GO
Or use the Surface Area Configuration tool to do the same thing
interactively.
RLF
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uxeNiReqHHA.4224@.TK2MSFTNGP02.phx.gbl...
> hi, I have a job failed for the following error. The job is send an email
> to me(outlook). any ideas?
> Thanks
> Executed as user: GOEVO\SRVAdmin. SQL Server blocked access to procedure
> 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this
> component is turned off as part of the security configuration for this
> server. A system administrator can enable the use of 'Ole Automation
> Procedures' by using sp_configure. For more information about enabling
> 'Ole Automation Procedures', see "Surface Area Configuration" in SQL
> Server Books Online. [SQLSTATE 42000] (Error 15281). The step failed.
>|||I am having the same problem here.
I have enabled OLE Automation, the services are running under local system
account, but i am still not able to create an object using sp_OACreate
the error message i am getting is
0x80010105 ODSOLE Extended Procedure The server threw an exception.
It is very nice error message that it won't give any idea :)
Thanks
Joko
"mecn" wrote:
> Thanks a lot. I apprecite
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23eAfSXeqHHA.4548@.TK2MSFTNGP03.phx.gbl...
> > mecn,
> >
> > Yes, the idea is to do what the messages says: "enable the use of 'Ole
> > Automation
> > Procedures' by using sp_configure."
> >
> > sp_configure 'Ole Automation Procedures', 1
> > GO
> > RECONFIGURE WITH OVERRIDE
> > GO
> >
> > Or use the Surface Area Configuration tool to do the same thing
> > interactively.
> >
> > RLF
> > "mecn" <mecn2002@.yahoo.com> wrote in message
> > news:uxeNiReqHHA.4224@.TK2MSFTNGP02.phx.gbl...
> >> hi, I have a job failed for the following error. The job is send an email
> >> to me(outlook). any ideas?
> >>
> >> Thanks
> >>
> >> Executed as user: GOEVO\SRVAdmin. SQL Server blocked access to procedure
> >> 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this
> >> component is turned off as part of the security configuration for this
> >> server. A system administrator can enable the use of 'Ole Automation
> >> Procedures' by using sp_configure. For more information about enabling
> >> 'Ole Automation Procedures', see "Surface Area Configuration" in SQL
> >> Server Books Online. [SQLSTATE 42000] (Error 15281). The step failed.
> >>
> >
> >
>
>