sql server dts: Run dts package in Stored Procedure and check for execution status -- sql server dts

Run dts package in Stored Procedure and check for execution status

loic_nospam NO[at]SPAM yahoo.com
10/21/2004 5:31:19 AM

sql server dts: Hi there, I hope someone can help.

I am trying to run a dts package in a stored procedure and check for
the execution status of the package (whether it failed or succeeded).

The dts package itself transfers data from Access to SQL server. Once
this is done, I want to run a query used for a report in the same
stored procedure that ran the package.

I know there is the 'ExecutionStatus' property for steps within a
package but what is the equivalent for the dts package itself?

Ideally, I think I'd like to use the OLE automation stored proc
sp_OAGetProperty.

Any ideas? Maybe that's not the best way to do it?

Thanks in advance for any help.

Re: Run dts package in Stored Procedure and check for execution status

Darren Green
10/21/2004 6:06:28 PM

There is no package level execution status, you need to check the states of
each step to determine the overall result, as your package may be considered
successfull even if a task fails. It may be clearer, and easier to detect
any failure if you set fail package on first error. For complicated packages
a step may execute several times, even failing some of those times so
checking the status at the end only gives you the result of teh last
execution. Not a problem for simple workflow of course.

OLE procs work-

Execute a package from T-SQL
(http://www.sqldts.com/default.aspx?210)

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

[quoted text, click to view]