March 23, 2012 at 6:39 am
I am running a stored proc as one of the steps in my ssis job:
ALTER PROCEDURE [dbo].[udsp_ApplyLogfileToRRDB]
-- Add the parameters for the stored procedure here
(
@p_filename varchar(max)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @p_path varchar(25)
DECLARE @cmd varchar(max)
SET @p_path = '\\BI01\PCCLogFiles\'
SET @cmd = '-SQL "RESTORE LOG [PCC-RRDB-CHG] FROM DISK = '''+@p_path + @p_filename+''' WITH STANDBY = ''G:\PCCBackupDONOTREMOVE\Undo_projects.dat'',PASSWORD = ''nothingToSeeHere:)'', DISCONNECT_EXISTING"';
EXECUTE master..sqlbackup @cmd
select @cmd
--EXECUTE master..sqlbackup @cmd;";
END
How can I capture a failure and stop if it fails? Right now it just runs and continues because I'm not looking for any failure notification.
March 23, 2012 at 7:22 am
Are you in the Control Flow using Execute SQL Task, or in the Data Flow using OLEDB Command?
March 23, 2012 at 7:24 am
It's an execute sql task step in the control flow. Sorry, probably should have included that 🙂
March 23, 2012 at 10:50 am
Set up a return value from the SP and add it to the Parameters page as a return value/local variable.
You can then use precedence constraints from that step to determine whether to move on or not based on that return value.
March 23, 2012 at 10:52 am
I guess that's my question, how do I return the failure in the stored proc? Maybe I should just look at return values in stored procs:)
March 23, 2012 at 3:46 pm
You could try
ALTER PROCEDURE [dbo].[udsp_ApplyLogfileToRRDB]
-- Add the parameters for the stored procedure here
(
@p_filename varchar(max)
)
AS
BEGIN
BEGIN TRY
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER()AS error_number --returns the number of the error.
, ERROR_SEVERITY() AS error_severity --returns the severity.
, ERROR_STATE()AS error_state --returns the error state number.
, ERROR_PROCEDURE() AS error_procedure --returns the name of the stored procedure or trigger where the error occurred.
, ERROR_LINE() AS error_line --returns the line number inside the routine that caused the error.
, ERROR_MESSAGE() AS error_message --returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.
END CATCH
END
March 23, 2012 at 5:23 pm
I'm not that familiar with redgate's sqlbackup proc, but if it throws and error you should be able to bubble that error up and handle in SSIS either through a result set or using a specific Return Code. You will need to configure the execute sql statement to accept those return values and on precedant constraint to the next item check the value of that value to ensure it shows success.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply