[scheduling] SSIS Package fails, but scheduler reports success

I have an SSIS Package that executes a stored procedure. It turns out my stored procedure was missing.

However, the SQL Job Scheduler reported that step as success.

Can anyone tell me why?

`Le

Because your package is not exiting with an indication of success/failure.

Moved to the SSIS forum.

|||
when I run it through the command line, it indeed gives me an error code of (1).

But the Scheduler still tells me that it is successful.

`Le
|||You need to check the JOB action to take when a step fails -perhaps it is set to continue and report success.|||The step itself is trying to run a stored procedure that does not exist. The Sql schedule should report a failure, but it is not. It reports a success.

The Sql Server job says "quit with failure" when there is an error. There is clearly a different problem here.

`Le

|||

It is difficult to help you when you have all of the details and we don't. We can't see what you are doing and what happens, Your descriptions are incomplete and do not allow a good problem determination and resolution.

Please post the entire command line that you are having problems with, including any success/failure statements after it executes.

Is this being executed by the Windows Scheduler?

What version of SQL Server are you using?

|||

Arnie Rowland wrote:

It is difficult to help you when you have all of the details and we don't. We can't see what you are doing and what happens, Your descriptions are incomplete and do not allow a good problem determination and resolution.

Please post the entire command line that you are having problems with, including any success/failure statements after it executes.

Is this being executed by the Windows Scheduler?

What version of SQL Server are you using?

Thank you for your reply. I will attempt to be more detailed:

SQL SERVER: 2005
VISUAL STUDIO: 2005
SSIS File Name: Foo.dtsx
Configuration File Name: Config.dtsConfig

This config file has multiple steps, including one step that executes a Stored Procedure.

When I run this through a command line, I run it as:
dtexec.exe /FILE "\\MyServer\Foo.dtsx.dtsx" /CONFIGFILE "\\MyServer\Config.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

When it is run this way, process exits with error (1), because the Stored Procedure in question does not exist on the database. This is the correct error message.

When I run it through SQL SERVER 2005 SCHEDULER, I run it as a SSIS package, with the command line:
/FILE "\\MyServer\Foo.dtsx.dtsx" /CONFIGFILE "\\MyServer\Config.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

However, the package always exits with success.

|||if a task inside a SSIS Package fails doesn't mean that the whole package fails
and the sql agent step would only report an error if the whole package fails

so you could set the property 'Fail Package on failure' to TRUE ... in the task where you execute this stored procedure.

- paul
|||When I run the package via command line (dtexec.exe), I do get fail message:

DTExec: The package execution returned DTSER_FAILURE(1)

~Le