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 stored proc with parameter

hi,

im getting an error when i run the stored proc with a string parameter in execute sql task object.

this is the only code i have:

exec sp_udt_keymaint 'table1'

I also set the 'Isstoredprocedure' in the properties as 'True' though, when you edit the execute sql task object, i can see that this parameter is disabled.

How do i do this right?

cherrie

Cherrie,

Not sure I am understanding the details of your problem (may be if you post the error you are geeting...); but here is my shot:

Assuming the parameter inside of your procedure is table1; then the SQLStatement of the SQL task should be something like:

EXEC sp_udt_keymaint @.table1=?

The in the parameter mapping page of the SQL task you have to map the SSIS variable to the SP parameter.

Rafael Salas

|||The syntax of the SQLStatement depends upon the connection type used. You may want to refer to BOL for the syntax of the each connection type.|||

Your best bet is to use a .NET provider. Judging by your procedure name, you're running a stored procedure on SQL Server. Then, set your SQLStatement to the name of your stored proc (dbo.sp_udt_keymaint). Add the parameter (@.TableName, the @. symbol is required) in the Parameter Mappings tab and map the parameter to a user-defined variable.

HTH

running stored proc with parameter

hi,

im getting an error when i run the stored proc with a string parameter in execute sql task object.

this is the only code i have:

exec sp_udt_keymaint 'table1'

I also set the 'Isstoredprocedure' in the properties as 'True' though, when you edit the execute sql task object, i can see that this parameter is disabled.

How do i do this right?

cherrie

Cherrie,

Not sure I am understanding the details of your problem (may be if you post the error you are geeting...); but here is my shot:

Assuming the parameter inside of your procedure is table1; then the SQLStatement of the SQL task should be something like:

EXEC sp_udt_keymaint @.table1=?

The in the parameter mapping page of the SQL task you have to map the SSIS variable to the SP parameter.

Rafael Salas

|||The syntax of the SQLStatement depends upon the connection type used. You may want to refer to BOL for the syntax of the each connection type.|||

Your best bet is to use a .NET provider. Judging by your procedure name, you're running a stored procedure on SQL Server. Then, set your SQLStatement to the name of your stored proc (dbo.sp_udt_keymaint). Add the parameter (@.TableName, the @. symbol is required) in the Parameter Mappings tab and map the parameter to a user-defined variable.

HTH

sql

Running SMO inside a SCRIPT TASK

Dear folks,

I'm trying to call SMO classes from SSIS package but I don't know which reference to use..

Mighn't be Imports Microsoft.SqlServer.Management? But Intellisense don't give me such info.

Let me know where I'm failing.

TIA

http://www.google.co.uk/search?hl=en&q=smo+namespace&meta=

-Jamie

|||

thanks Jamie,

It seems that must be moved some assemblies to \Microsoft.NET\Framework\v2.0.xxxxx in order to acquire its full features from a package.

|||

Correct. But only at design-time

http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2341.aspx

-Jamie

|||

thanks for the response but I don't get the point colleague.

What do you mean in design-time? I want to instantiate SMO classes from Script Task. I know that I can do smo stuff by mean Foreach Loop Container and using as enumerator SMO but my goal is run (as I did before with sql2k and DMO throught Visual Basic Script) SMO fully from scripting

|||

You only need the DLLs in \Microsoft.NET\Framework\v2.0.xxxxx when you are desinging the package. When you run it they don't need to be there.

This is an important consideration when you deploy your packages because it means you don't have to deploy DLLs to \Microsoft.NET\Framework\v2.0.xxxxx on the server on which you run your packages.

-Jamie

|||

ok, thanks again.

But anyway no namespaces appears in my Script Task about SMO after that.

|||

Did you add a reference and add the Imports statement?

-Jamie

|||thanks a lot!!!!!!!! you must be tons of patience|||

enric vives wrote:

thanks a lot!!!!!!!! you must be tons of patience

I'm sure my girlfriend wouldn't say that !!!

-Jamie

|||Well then I hope she is not a programmer/dba or something like that..|||

Definately definately not!

|||!!