DTSX Package failure returning 0

I have a Web App that I'm trying to launch a DTSX pack with. I wrote
a Stored Procedure that executes a job that launches the package.
CODE:
Execute msdb.dbo.sp_start_Job
'Import_Contractor_Employee_Data_to_CCTS'
When everything is correct, it runs good. I found out this moring
that there is one potential situation that will cause the package to
fail. The problem is, when the package fails...with the above code,
it returns the below if I run it as a single line:
Job 'Import_Contractor_Employee_Data_to_CCTS' started successfully.
If I execute it through the complete stored procedure...:
ALTER PROCEDURE [dbo].[ASP_CCTS_RunImportContractorEmployee_Job]
AS
DECLARE @.intErrorCode int
BEGIN
SET NOCOUNT ON;
Execute @.intErrorCode = msdb.dbo.sp_start_Job
'Import_Contractor_Employee_Data_to_CCTS'
Select @.intErrorCode Error_Code
END
It returns a 0 as though nothing is wrong. What am I missing here?
ThanksHi Dave
You status says that the job started ok, not that it completed ok. Look at
sysjobs and you can see if it is currently executing and wait until it
finishes before checking the status.
John
"Dave" wrote:
> I have a Web App that I'm trying to launch a DTSX pack with. I wrote
> a Stored Procedure that executes a job that launches the package.
> CODE:
> Execute msdb.dbo.sp_start_Job
> 'Import_Contractor_Employee_Data_to_CCTS'
>
> When everything is correct, it runs good. I found out this moring
> that there is one potential situation that will cause the package to
> fail. The problem is, when the package fails...with the above code,
> it returns the below if I run it as a single line:
> Job 'Import_Contractor_Employee_Data_to_CCTS' started successfully.
> If I execute it through the complete stored procedure...:
> ALTER PROCEDURE [dbo].[ASP_CCTS_RunImportContractorEmployee_Job]
> AS
> DECLARE @.intErrorCode int
> BEGIN
> SET NOCOUNT ON;
> Execute @.intErrorCode = msdb.dbo.sp_start_Job
> 'Import_Contractor_Employee_Data_to_CCTS'
> Select @.intErrorCode Error_Code
> END
>
> It returns a 0 as though nothing is wrong. What am I missing here?
>
> Thanks
>