Execute DTS Package Through T-SQL

#1

http://www.sqldts.com/default.aspx?104

This URL was extremely helpful, however I am having difficulties with the
example provided.

The example loops through the steps within the DTS Package to return the
result of the steps in the DTS package. I have a conditional branch in the
DTS package to send me an email if a step fails. Since that step was never
executed it appears that the return value (@StepResult) from
EXEC @hr = sp_OAGetProperty @Step, 'ExecutionResult', @StepResult OUTPUT
is equal to a 1, which indicates that the step failed.

The step never executed. Is there another property or something that would
let me know that the step didn't execute so I can modify the result from the
above code. When I look at the log created by the package, I can see that
the step x 'was not executed'

Thank you in advance.

#2

You should be able to can check the ExecutionStatus property as well to see
if it has been run. If not no need to check the result.

However no method will work for all scenarios though, as the status values
are a snapshot at the end of execution. You can execute tasks multiple times
and with complicated workflow it doesn't hold true. Only the first step in
an inactive branch is set as Inactive as well.

--
Darren Green
http://www.sqldts.com
http://www.sqlis.com

"Stephen Costanzo" <sxcostanzo (AT) hotmail (DOT) com> wrote

Quote:

http://www.sqldts.com/default.aspx?104

This URL was extremely helpful, however I am having difficulties with the
example provided.

The example loops through the steps within the DTS Package to return the
result of the steps in the DTS package. I have a conditional branch in the
DTS package to send me an email if a step fails. Since that step was never
executed it appears that the return value (@StepResult) from
EXEC @hr = sp_OAGetProperty @Step, 'ExecutionResult', @StepResult OUTPUT
is equal to a 1, which indicates that the step failed.

The step never executed. Is there another property or something that would
let me know that the step didn't execute so I can modify the result from
the
above code. When I look at the log created by the package, I can see that
the step x 'was not executed'

Thank you in advance.

#3

Thank you - this nailed it!

Followup question : How do you know all of the properties of the
sp_OAGetProperty as it relates to packages? If I could have read that I
might have eventually gotten there.

Thanks
"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote

Quote:

You should be able to can check the ExecutionStatus property as well to
see
if it has been run. If not no need to check the result.

However no method will work for all scenarios though, as the status values
are a snapshot at the end of execution. You can execute tasks multiple
times
and with complicated workflow it doesn't hold true. Only the first step in
an inactive branch is set as Inactive as well.

--
Darren Green
http://www.sqldts.com
http://www.sqlis.com

"Stephen Costanzo" <sxcostanzo (AT) hotmail (DOT) com> wrote in message
news:uAeqOY1pFHA.3800 (AT) TK2MSFTNGP11 (DOT) phx.gbl...
http://www.sqldts.com/default.aspx?104

This URL was extremely helpful, however I am having difficulties with
the
example provided.

The example loops through the steps within the DTS Package to return the
result of the steps in the DTS package. I have a conditional branch in
the
DTS package to send me an email if a step fails. Since that step was
never
executed it appears that the return value (@StepResult) from
EXEC @hr = sp_OAGetProperty @Step, 'ExecutionResult', @StepResult
OUTPUT
is equal to a 1, which indicates that the step failed.

The step never executed. Is there another property or something that
would
let me know that the step didn't execute so I can modify the result from
the
above code. When I look at the log created by the package, I can see
that
the step x 'was not executed'

Thank you in advance.

#4

Are you asking how I know there are properties such as ExecutionResult and
ExecutionStatus, and what objects they apply to? If so then, look in Books
Online. If you start by looking up the "Package objects" in the index, you
will see them all listed. Look at Package and you can see the object model
hierarchy and work down.

Darren

"Stephen Costanzo" <sxcostanzo (AT) hotmail (DOT) com> wrote

Quote:

Thank you - this nailed it!

Followup question : How do you know all of the properties of the
sp_OAGetProperty as it relates to packages? If I could have read that I
might have eventually gotten there.

Thanks
"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote in
message
news:eEpcnG8pFHA.2956 (AT) TK2MSFTNGP12 (DOT) phx.gbl...
You should be able to can check the ExecutionStatus property as well to
see
if it has been run. If not no need to check the result.

However no method will work for all scenarios though, as the status
values
are a snapshot at the end of execution. You can execute tasks multiple
times
and with complicated workflow it doesn't hold true. Only the first step
in
an inactive branch is set as Inactive as well.

--
Darren Green
http://www.sqldts.com
http://www.sqlis.com

"Stephen Costanzo" <sxcostanzo (AT) hotmail (DOT) com> wrote in message
news:uAeqOY1pFHA.3800 (AT) TK2MSFTNGP11 (DOT) phx.gbl...
http://www.sqldts.com/default.aspx?104

This URL was extremely helpful, however I am having difficulties with
the
example provided.

The example loops through the steps within the DTS Package to return
the
result of the steps in the DTS package. I have a conditional branch in
the
DTS package to send me an email if a step fails. Since that step was
never
executed it appears that the return value (@StepResult) from
EXEC @hr = sp_OAGetProperty @Step, 'ExecutionResult', @StepResult
OUTPUT
is equal to a 1, which indicates that the step failed.

The step never executed. Is there another property or something that
would
let me know that the step didn't execute so I can modify the result
from
the
above code. When I look at the log created by the package, I can see
that
the step x 'was not executed'

Thank you in advance.

#5

Thanks.

"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote

Quote:

Are you asking how I know there are properties such as ExecutionResult and
ExecutionStatus, and what objects they apply to? If so then, look in Books
Online. If you start by looking up the "Package objects" in the index, you
will see them all listed. Look at Package and you can see the object model
hierarchy and work down.

Darren

"Stephen Costanzo" <sxcostanzo (AT) hotmail (DOT) com> wrote in message
news:e8ziGeAqFHA.3520 (AT) tk2msftngp13 (DOT) phx.gbl...
Thank you - this nailed it!

Followup question : How do you know all of the properties of the
sp_OAGetProperty as it relates to packages? If I could have read that I
might have eventually gotten there.

Thanks
"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote in
message
news:eEpcnG8pFHA.2956 (AT) TK2MSFTNGP12 (DOT) phx.gbl...
You should be able to can check the ExecutionStatus property as well to
see
if it has been run. If not no need to check the result.

However no method will work for all scenarios though, as the status
values
are a snapshot at the end of execution. You can execute tasks multiple
times
and with complicated workflow it doesn't hold true. Only the first step
in
an inactive branch is set as Inactive as well.

--
Darren Green
http://www.sqldts.com
http://www.sqlis.com

"Stephen Costanzo" <sxcostanzo (AT) hotmail (DOT) com> wrote in message
news:uAeqOY1pFHA.3800 (AT) TK2MSFTNGP11 (DOT) phx.gbl...
http://www.sqldts.com/default.aspx?104

This URL was extremely helpful, however I am having difficulties with
the
example provided.

The example loops through the steps within the DTS Package to return
the
result of the steps in the DTS package. I have a conditional branch
in
the
DTS package to send me an email if a step fails. Since that step was
never
executed it appears that the return value (@StepResult) from
EXEC @hr = sp_OAGetProperty @Step, 'ExecutionResult', @StepResult
OUTPUT
is equal to a 1, which indicates that the step failed.

The step never executed. Is there another property or something that
would
let me know that the step didn't execute so I can modify the result
from
the
above code. When I look at the log created by the package, I can see
that
the step x 'was not executed'

Thank you in advance.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Execute DTS Package Through T-SQL

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Execute DTS Package Through T-SQL - 08-22-2005 , 03:10 PM

Re: Execute DTS Package Through T-SQL - 08-23-2005 , 04:04 AM

Re: Execute DTS Package Through T-SQL - 08-23-2005 , 12:21 PM

Re: Execute DTS Package Through T-SQL - 08-24-2005 , 03:28 AM

Re: Execute DTS Package Through T-SQL - 08-25-2005 , 12:13 PM