Capturing Execution Results

Hi All

When running an SSIS Package from BIDS, we get to see the "Progress" tab which explains us the progress of the SSIS Package. During production, is there any way to capture this log. I am interested in using this as a log file for each run of my SSIS package.

Thanks,

S Suresh

Have you tried to enable logging in the package?

Thanks,
Ovidiu Burlacu

|||

I tried loging. But I want some Progress Reporting similar to one we see in the Execution Results. Any thoughts on this?

Thanks,

S Suresh

|||

Suresh S wrote:

I tried loging. But I want some Progress Reporting similar to one we see in the Execution Results. Any thoughts on this?

Thanks,

S Suresh

The information in the logfile is exactly the same as in teh Prgress tab.

|||

Suresh,

Just in case, here they are talking about package loging and not about SQL Server Agent loging. Package loging as it is being said, it will provide what you need.

Rafael Salas

|||

Hello,

I am not sure, I understand the difference between the two and would appreciate it if you could clarify how to implement package logging as opposed to SQL Server Agent Logging. I have enabled the package logging property, however, would like to obtain a file that is in the same format as that displayed in the 'Execution Results/Progress' tab.

Thanks,

Subah.

|||

Subah wrote:

Hello,

I am not sure, I understand the difference between the two and would appreciate it if you could clarify how to implement package logging as opposed to SQL Server Agent Logging. I have enabled the package logging property, however, would like to obtain a file that is in the same format as that displayed in the 'Execution Results/Progress' tab.

Thanks,

Subah.

You won't get a file that looks the same as that tab, but you WILL get a file with the same information (actually that's not strictly true - I know of one thing you get extra in the 'Execution Results/Progress' tab but its not something you need). It just won't be presented as nicely as it is in the 'Execution Results/Progress' tab, that's all.

-Jamie

|||Thanks Jamie. I guess I am on the right track then. :-)

Capturing Execution Results

Hi All

When running an SSIS Package from BIDS, we get to see the "Progress" tab which explains us the progress of the SSIS Package. During production, is there any way to capture this log. I am interested in using this as a log file for each run of my SSIS package.

Thanks,

S Suresh

Have you tried to enable logging in the package?

Thanks,
Ovidiu Burlacu

|||

I tried loging. But I want some Progress Reporting similar to one we see in the Execution Results. Any thoughts on this?

Thanks,

S Suresh

|||

Suresh S wrote:

I tried loging. But I want some Progress Reporting similar to one we see in the Execution Results. Any thoughts on this?

Thanks,

S Suresh

The information in the logfile is exactly the same as in teh Prgress tab.

|||

Suresh,

Just in case, here they are talking about package loging and not about SQL Server Agent loging. Package loging as it is being said, it will provide what you need.

Rafael Salas

|||

Hello,

I am not sure, I understand the difference between the two and would appreciate it if you could clarify how to implement package logging as opposed to SQL Server Agent Logging. I have enabled the package logging property, however, would like to obtain a file that is in the same format as that displayed in the 'Execution Results/Progress' tab.

Thanks,

Subah.

|||

Subah wrote:

Hello,

I am not sure, I understand the difference between the two and would appreciate it if you could clarify how to implement package logging as opposed to SQL Server Agent Logging. I have enabled the package logging property, however, would like to obtain a file that is in the same format as that displayed in the 'Execution Results/Progress' tab.

Thanks,

Subah.

You won't get a file that looks the same as that tab, but you WILL get a file with the same information (actually that's not strictly true - I know of one thing you get extra in the 'Execution Results/Progress' tab but its not something you need). It just won't be presented as nicely as it is in the 'Execution Results/Progress' tab, that's all.

-Jamie

|||Thanks Jamie. I guess I am on the right track then. :-)

Capture name of stored procedure within itself?

Is there any way of capturing the name of a stored procedure during
execution?
I've created a generic stored procedure to handle much of my error
handling. I would like to be able to have the calling stored
procedure to pass its name as one of the parameters to the error
handling proc to be logged on certain error messages events.
I could easily hard code the stored procedure name on all of the
calls, but I am trying to create a series of generic code blocks to
add to the stored procedures (one for simple SELECTS, UPDATE, INSERTS,
ETC), so I was hoping to find a function or create a function that
would give the name of the proc.
Any thoughts would be appreciated.SELECT OBJECT_NAME(@.@.PROCID) AS ProcName
"Sean O'Thule" <othule@.hotmail.com> wrote in message
news:63840202.0408300912.2d9c1f2c@.posting.google.com...
> Is there any way of capturing the name of a stored procedure during
> execution?
> I've created a generic stored procedure to handle much of my error
> handling. I would like to be able to have the calling stored
> procedure to pass its name as one of the parameters to the error
> handling proc to be logged on certain error messages events.
> I could easily hard code the stored procedure name on all of the
> calls, but I am trying to create a series of generic code blocks to
> add to the stored procedures (one for simple SELECTS, UPDATE, INSERTS,
> ETC), so I was hoping to find a function or create a function that
> would give the name of the proc.
> Any thoughts would be appreciated.|||Great, thanks

Capture Execution Time Then Rollback Transaction

I would like to execute a stored procedure listed below then capture or
print out the execution time. Lastly rollback the transaction so that data
does not change in the database.
dbo.usp_Manual_toTraint 65823,'2004-08-01','TA_BB','2004-09-01'
Please help me with this procedure.
Thanks,Joe
See if this helps you
declare @.dt datetime
set @.dt =getdate()
begin tran
--do something here
rollback
select datediff(ss,@.dt,getdate())
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:F6B145E9-8520-4DF3-95ED-1E4BC20E5DFE@.microsoft.com...
> I would like to execute a stored procedure listed below then capture or
> print out the execution time. Lastly rollback the transaction so that
> data
> does not change in the database.
> dbo.usp_Manual_toTraint 65823,'2004-08-01','TA_BB','2004-09-01'
> Please help me with this procedure.
> Thanks,|||Uri Dimant (urid@.iscar.co.il) writes:
> Joe
> See if this helps you
> declare @.dt datetime
> set @.dt =getdate()
> begin tran
> --do something here
> rollback
> select datediff(ss,@.dt,getdate())
That's not good. You need to do:
declare @.dt datetime
set @.dt =getdate()
begin tran
--do something here
select datediff(ss,@.dt,getdate())
rollback
ROLLBACK can take considerable time and should not be measured.
Also, in many situations, ms (milliseconds) is better than ss (seconds).
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Can't use Profiler to get execution plan for a particular query

I am trying to troubleshoot some performance problems and I am trying
to set up Profiler so that I can look at the execution plan of a
particular stored procedure. I am using the ExecutionPlan event class
in the Performance event category. I am also setting up a filter on
ObjectID so that only activity related to this particular store
procedure is returned.
However Profiler is capturing ALL the activity on the server. Filtering
by ObjectID doesn't seem to make any difference even though according
to BOL the ObjectID is a valid data column for the ExecutionPlan event.
Am I missing something or is this a bug in SQL Server/error in the BOL
documentation?
Thanks!Are you saying that you are also capturing System IDs , if you put ObjectID
greater than or equal to 100 - this will disclude System Objects.
This may not be complteley relevant to your problem , but you could also use
the DatabaseID / DatabaseName filter
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
<pshroads@.gmail.com> wrote in message
news:1150241832.487422.17030@.y43g2000cwc.googlegroups.com...
> I am trying to troubleshoot some performance problems and I am trying
> to set up Profiler so that I can look at the execution plan of a
> particular stored procedure. I am using the ExecutionPlan event class
> in the Performance event category. I am also setting up a filter on
> ObjectID so that only activity related to this particular store
> procedure is returned.
> However Profiler is capturing ALL the activity on the server. Filtering
> by ObjectID doesn't seem to make any difference even though according
> to BOL the ObjectID is a valid data column for the ExecutionPlan event.
> Am I missing something or is this a bug in SQL Server/error in the BOL
> documentation?
> Thanks!
>|||The ExecutionPlan event class doesn't populate the object id unfortunately.
So, we cannot filter on it in SQL Server 2000 Profiler. But it does populate
the database id, so you can filter on that to reduce the amount of data
being captured by Profiler. You can also filter on the hostname, if you know
from which machine the request is coming in.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<pshroads@.gmail.com> wrote in message
news:1150241832.487422.17030@.y43g2000cwc.googlegroups.com...
I am trying to troubleshoot some performance problems and I am trying
to set up Profiler so that I can look at the execution plan of a
particular stored procedure. I am using the ExecutionPlan event class
in the Performance event category. I am also setting up a filter on
ObjectID so that only activity related to this particular store
procedure is returned.
However Profiler is capturing ALL the activity on the server. Filtering
by ObjectID doesn't seem to make any difference even though according
to BOL the ObjectID is a valid data column for the ExecutionPlan event.
Am I missing something or is this a bug in SQL Server/error in the BOL
documentation?
Thanks!|||Are you saying that you are also capturing System IDs , if you put ObjectID
greater than or equal to 100 - this will disclude System Objects.
This may not be complteley relevant to your problem , but you could also use
the DatabaseID / DatabaseName filter
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
<pshroads@.gmail.com> wrote in message
news:1150241832.487422.17030@.y43g2000cwc.googlegroups.com...
> I am trying to troubleshoot some performance problems and I am trying
> to set up Profiler so that I can look at the execution plan of a
> particular stored procedure. I am using the ExecutionPlan event class
> in the Performance event category. I am also setting up a filter on
> ObjectID so that only activity related to this particular store
> procedure is returned.
> However Profiler is capturing ALL the activity on the server. Filtering
> by ObjectID doesn't seem to make any difference even though according
> to BOL the ObjectID is a valid data column for the ExecutionPlan event.
> Am I missing something or is this a bug in SQL Server/error in the BOL
> documentation?
> Thanks!
>|||The ExecutionPlan event class doesn't populate the object id unfortunately.
So, we cannot filter on it in SQL Server 2000 Profiler. But it does populate
the database id, so you can filter on that to reduce the amount of data
being captured by Profiler. You can also filter on the hostname, if you know
from which machine the request is coming in.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<pshroads@.gmail.com> wrote in message
news:1150241832.487422.17030@.y43g2000cwc.googlegroups.com...
I am trying to troubleshoot some performance problems and I am trying
to set up Profiler so that I can look at the execution plan of a
particular stored procedure. I am using the ExecutionPlan event class
in the Performance event category. I am also setting up a filter on
ObjectID so that only activity related to this particular store
procedure is returned.
However Profiler is capturing ALL the activity on the server. Filtering
by ObjectID doesn't seem to make any difference even though according
to BOL the ObjectID is a valid data column for the ExecutionPlan event.
Am I missing something or is this a bug in SQL Server/error in the BOL
documentation?
Thanks!|||Thanks for your reply. Do you know of any way to get the execution plan
for a query that is already running? Our database is too active to run
a profiler for an extended period of time to catch this intermittent
problem but when it's occuring I'd like to be able to see the query
plan that it's using.
Thanks|||Thanks for your reply. Do you know of any way to get the execution plan
for a query that is already running? Our database is too active to run
a profiler for an extended period of time to catch this intermittent
problem but when it's occuring I'd like to be able to see the query
plan that it's using.
Thanks|||I am not aware of a way of getting execution plan of a query while it is
running, without using Profiler.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<pshroads@.gmail.com> wrote in message
news:1150323592.065493.262700@.f6g2000cwb.googlegroups.com...
Thanks for your reply. Do you know of any way to get the execution plan
for a query that is already running? Our database is too active to run
a profiler for an extended period of time to catch this intermittent
problem but when it's occuring I'd like to be able to see the query
plan that it's using.
Thanks|||I am not aware of a way of getting execution plan of a query while it is
running, without using Profiler.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<pshroads@.gmail.com> wrote in message
news:1150323592.065493.262700@.f6g2000cwb.googlegroups.com...
Thanks for your reply. Do you know of any way to get the execution plan
for a query that is already running? Our database is too active to run
a profiler for an extended period of time to catch this intermittent
problem but when it's occuring I'd like to be able to see the query
plan that it's using.
Thanks|||Sorry I should have clarified - even Profiler won't give me the
execution plan for a query that is already running when I start the
trace, correct? I would have to already have the trace running first.

Can't use Profiler to get execution plan for a particular query

I am trying to troubleshoot some performance problems and I am trying
to set up Profiler so that I can look at the execution plan of a
particular stored procedure. I am using the ExecutionPlan event class
in the Performance event category. I am also setting up a filter on
ObjectID so that only activity related to this particular store
procedure is returned.
However Profiler is capturing ALL the activity on the server. Filtering
by ObjectID doesn't seem to make any difference even though according
to BOL the ObjectID is a valid data column for the ExecutionPlan event.
Am I missing something or is this a bug in SQL Server/error in the BOL
documentation?
Thanks!Are you saying that you are also capturing System IDs , if you put ObjectID
greater than or equal to 100 - this will disclude System Objects.
This may not be complteley relevant to your problem , but you could also use
the DatabaseID / DatabaseName filter
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
<pshroads@.gmail.com> wrote in message
news:1150241832.487422.17030@.y43g2000cwc.googlegroups.com...
> I am trying to troubleshoot some performance problems and I am trying
> to set up Profiler so that I can look at the execution plan of a
> particular stored procedure. I am using the ExecutionPlan event class
> in the Performance event category. I am also setting up a filter on
> ObjectID so that only activity related to this particular store
> procedure is returned.
> However Profiler is capturing ALL the activity on the server. Filtering
> by ObjectID doesn't seem to make any difference even though according
> to BOL the ObjectID is a valid data column for the ExecutionPlan event.
> Am I missing something or is this a bug in SQL Server/error in the BOL
> documentation?
> Thanks!
>|||The ExecutionPlan event class doesn't populate the object id unfortunately.
So, we cannot filter on it in SQL Server 2000 Profiler. But it does populate
the database id, so you can filter on that to reduce the amount of data
being captured by Profiler. You can also filter on the hostname, if you know
from which machine the request is coming in.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<pshroads@.gmail.com> wrote in message
news:1150241832.487422.17030@.y43g2000cwc.googlegroups.com...
I am trying to troubleshoot some performance problems and I am trying
to set up Profiler so that I can look at the execution plan of a
particular stored procedure. I am using the ExecutionPlan event class
in the Performance event category. I am also setting up a filter on
ObjectID so that only activity related to this particular store
procedure is returned.
However Profiler is capturing ALL the activity on the server. Filtering
by ObjectID doesn't seem to make any difference even though according
to BOL the ObjectID is a valid data column for the ExecutionPlan event.
Am I missing something or is this a bug in SQL Server/error in the BOL
documentation?
Thanks!|||Thanks for your reply. Do you know of any way to get the execution plan
for a query that is already running? Our database is too active to run
a profiler for an extended period of time to catch this intermittent
problem but when it's occuring I'd like to be able to see the query
plan that it's using.
Thanks|||I am not aware of a way of getting execution plan of a query while it is
running, without using Profiler.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<pshroads@.gmail.com> wrote in message
news:1150323592.065493.262700@.f6g2000cwb.googlegroups.com...
Thanks for your reply. Do you know of any way to get the execution plan
for a query that is already running? Our database is too active to run
a profiler for an extended period of time to catch this intermittent
problem but when it's occuring I'd like to be able to see the query
plan that it's using.
Thanks|||Sorry I should have clarified - even Profiler won't give me the
execution plan for a query that is already running when I start the
trace, correct? I would have to already have the trace running first.|||One way of doing this is to inject some sp_trace_create script into the
stored proc from which you want to capture exec plans, using @.@.spid as a
filter. Start the trace at the beginning of the proc & close it at the end.
If the proc is a high repitition proc, you might need some logic to limit
how many instances actually get traced, but this isn't too hard to work
out..
Regards,
Greg Linwood
SQL Server MVP
<pshroads@.gmail.com> wrote in message
news:1150387360.417932.118480@.h76g2000cwa.googlegroups.com...
> Sorry I should have clarified - even Profiler won't give me the
> execution plan for a query that is already running when I start the
> trace, correct? I would have to already have the trace running first.
>|||That's a great idea! I will try that.
Thanks.|||Remember to capture Showplan ALL, so you can pick apart the execution plan
subtree cost & focus your tuning work similarly to how you would if you were
using a graphical plan (based on node %). Post back if you have any
difficulty analysing the text output as that can also be tricky sometimes..
Regards,
Greg Linwood
SQL Server MVP
<pshroads@.gmail.com> wrote in message
news:1150465481.039455.258630@.u72g2000cwu.googlegroups.com...
> That's a great idea! I will try that.
> Thanks.
>