RAISERROR does not cause SQL task to fail - why?

Greetings,

I have a stored procedure with a TRY / CATCH block. In the catch block I capture information about the error. I then use RAISERROR to "rethrow" the exception so that it will be available to SSIS.

I execute the stored procedure through a SQL task. I observe that SSIS reports the SQL task succeeds (the task box turns green) when RAISERROR is invoked. If I comment the catch block with RAISERROR then SSIS reports the task failed. (I created a simple procedure that does a divide by zero to force an error.) The expected error message is displayed when the sproc is run from the SQL Server Management Studio command line so I believe that the stored procedure is doing what I intended.

I would like to handle an error within my stored procedure without destroying SSIS's ability to detect a task failure. Is this possible? Is there an alternative to using RAISERROR?

Thanks,

BCB

But is it a message or an output? That is, which tab in SSMS does the results of the procedure end up on? "Results" or "Messages"?|||Can you please post code for both versions of the stored procedure?|||

Thanks for the replies.

Phil, to answer your question, the error message is displayed on the Messages tab in Management Studio.

I'm including two versions of the sproc as well as the invocation code I used to run it within Management Studio. I'm successfuly capturing the error info in the CATCH block and passing it back to SSIS. The point of having the RAISERROR at the end of the CATCH is to make SSIS fail the task.

This is the sproc with no CATCH:

Code Snippet

-- This is the stripped procedure with no CATCH block. The OUTPUT parms do nothing.
-- This sproc will cause an SQL task to fail.
ALTER PROCEDURE [dbo].[sp_ThrowException]
@.ERROR_NUMBER INT OUTPUT,
@.ERROR_MESSAGE NVARCHAR(4000) OUTPUT,
@.ERROR_SEVERITY INT OUTPUT,
@.ERROR_STATE INT OUTPUT,
@.ERROR_PROCEDURE NVARCHAR(126) OUTPUT,
@.ERROR_LINE INT OUTPUT,
@.FormattedMessage NVARCHAR(4000) OUTPUT
AS

BEGIN

SET NOCOUNT ON

-- this forces a "divide by zero" exception
SELECT 1 / 0 AS 'TRY' FROM view_OilRigs_REPORT R

END

This is the sproc as I really want it. It has the RAISERROR in the CATCH block.

Code Snippet

-- This is the desired procedure with the CATCH block. The error information is being returned
-- to the SQL task through the OUTPUT parms.
-- This sproc will allow an SQL task to succeed.

ALTER PROCEDURE [dbo].[sp_ThrowException]
@.ERROR_NUMBER INT OUTPUT,
@.ERROR_MESSAGE NVARCHAR(4000) OUTPUT,
@.ERROR_SEVERITY INT OUTPUT,
@.ERROR_STATE INT OUTPUT,
@.ERROR_PROCEDURE NVARCHAR(126) OUTPUT,
@.ERROR_LINE INT OUTPUT,
@.FormattedMessage NVARCHAR(4000) OUTPUT
AS

BEGIN TRY

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

-- this forces a "divide by zero" exception
SELECT 1 / 0 AS 'TRY' FROM view_OilRigs_REPORT R

END TRY

BEGIN CATCH

SELECT
@.ERROR_NUMBER = ERROR_NUMBER(),
@.ERROR_MESSAGE = ERROR_MESSAGE(),
@.ERROR_SEVERITY = ERROR_SEVERITY(),
@.ERROR_STATE = ERROR_STATE(),
@.ERROR_PROCEDURE = ERROR_PROCEDURE(),
@.ERROR_LINE = ERROR_LINE(),
-- this renders the error message in command line format
@.FormattedMessage = 'Msg ' + CAST(ERROR_NUMBER() AS NVARCHAR(20)) + ', ' +
'Level ' + CAST(ERROR_SEVERITY() AS NVARCHAR(20)) + ', ' +
'State ' + CAST(ERROR_STATE() AS NVARCHAR(20)) + ', ' +
'Procedure ' + ERROR_PROCEDURE() + ', ' +
'Line ' + CAST(ERROR_LINE() AS NVARCHAR(20)) + ', ' +
'Message: ' + ERROR_MESSAGE()

-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR
(
@.ERROR_MESSAGE, -- Message text
@.ERROR_SEVERITY, -- Severity
@.ERROR_STATE -- State
)

END CATCH

I use this code to run the sproc in Management Studio.

Code Snippet

-- I use this to run the sproc within Management Studio.

SET NOCOUNT ON;

DECLARE @.local_ERROR_NUMBER INT
DECLARE @.local_ERROR_MESSAGE NVARCHAR(4000)
DECLARE @.local_ERROR_SEVERITY INT
DECLARE @.local_ERROR_STATE INT
DECLARE @.local_ERROR_PROCEDURE NVARCHAR(126)
DECLARE @.local_ERROR_LINE INT
DECLARE @.local_FormattedMessage NVARCHAR(4000)

EXEC [dbo].[sp_ThrowException]
@.ERROR_NUMBER = @.local_ERROR_NUMBER OUTPUT,
@.ERROR_MESSAGE = @.local_ERROR_MESSAGE OUTPUT,
@.ERROR_SEVERITY = @.local_ERROR_SEVERITY OUTPUT,
@.ERROR_STATE = @.local_ERROR_STATE OUTPUT,
@.ERROR_PROCEDURE = @.local_ERROR_PROCEDURE OUTPUT,
@.ERROR_LINE = @.local_ERROR_LINE OUTPUT,
@.FormattedMessage = @.local_FormattedMessage OUTPUT
SELECT
@.local_ERROR_NUMBER AS ERROR_NUMBER,
@.local_ERROR_MESSAGE AS ERROR_MESSAGE,
@.local_ERROR_SEVERITY AS ERROR_SEVERITY,
@.local_ERROR_STATE AS ERROR_STATE,
@.local_ERROR_PROCEDURE AS ERROR_PROCEDURE,
@.local_ERROR_LINE AS ERROR_LINE,
@.local_FormattedMessage AS FormattedMessage

Thanks for looking at this.

BCB

|||As long as it's posted to the messages tab, to my knowledge, you cannot capture that inside SSIS.|||

BlackCatBone wrote:

Thanks for the replies.

Phil, to answer your question, the error message is displayed on the Messages tab in Management Studio.

I'm including two versions of the sproc as well as the invocation code I used to run it within Management Studio. I'm successfuly capturing the error info in the CATCH block and passing it back to SSIS. The point of having the RAISERROR at the end of the CATCH is to make SSIS fail the task.

I don't think you can have it both ways. If the task fails, you're not going to be able to use it to capture the output parameters.

I'm personally surprised that SSIS does not fail the task when you use RAISERROR in your procedure code. I would have assumed that explicitly raising an error would cause the task to fail, but it does not. It's far too late for me to dig into this. Perhaps someone else can shed some light on the "why" of this behavior.

With that said, this is the approach that I would recommend if I needed to capture the error information from the stored procedure and have the package respond to the error as well:

Remove the RAISERROR from the procedure. Leave the TRY CATCH block in the procedure. Have the Execute SQL task call the procedure and capture the output parameters. Have precedence constraints below the Execute SQL task that follow a "success" path or an "error" path based on the values of the output parameters - likely the error number.

R/W access problem with var in script task

Hi there

I have a a global variable for the package.

Now in script task of data flow I am trying to assign some value to it.

I have also defined same variable in Read/Write property of script task.

But at run time it gives me following error:

"The collection of variables locked for read and write access is not available outside of PostExecute."

How do we resolve this?

Thanks and Regards

Rahul Kumar

And in which overrided Sub are you trying to access this variable?

-Tom

|||

Hi Tom

I am doing all this in

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

|||

Try this:

Public Overloads Overrides Sub ProcessInput(ByVal inputID As Integer, ByVal buffer As PipelineBuffer)
If Buffer Is Nothing Then
Throw New ArgumentNullException("buffer")
End If
Dim vars As IDTSVariables90
Dim varServerName As IDTSVariable90
Dim varPackageName As IDTSVariable90

....

Try

...
Me.VariableDispenser.LockForRead("ServerName")

...
Me.VariableDispenser.GetVariables(vars)
varServerName = vars.Item(0) (0 because this was the first variable you've locked)

...

catch ....

|||

You can find lots of examples by just typing in msnsearch "using variable in script component"

like for example:

http://msdn2.microsoft.com/en-us/library/aa337079.aspx

-Tom

|||

Tom, your code sample is not actually for a Script Component, rather a full custom component.

Rahul, to be clear, when using the variable lock lists provide in the UI of the the Script Component you cannot access variables in the process input member (MyBuffer_ProcessInputRow). You can access read-only variables in the PreExecute and read-write variables in PostExecute

From the BOL link in Tom's second post -

Important:

The collection of ReadWriteVariables is only available in the PostExecute method to maximize performance and minimize the risk of locking conflicts.

If you do the locking yourself though you get more control. Try this sample code, but create the variables first if you try and run it.

Public Class ScriptMain

Inherits UserComponent

Private counter As Integer

Public Overrides Sub PreExecute()

' Initialise Counter from RO variable

counter = Me.Variables.VariableRO

MyBase.PreExecute()

End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

' Increment Counter

counter = counter + 1

' Use manual locking to set variable in Process Input

Dim variables As IDTSVariables90

Me.VariableDispenser.LockOneForRead("VariableRWManual", variables)

variables(0).Value = 125

variables.Unlock()

End Sub

Public Overrides Sub PostExecute()

' Store updated counter in RW variable

Me.Variables.VariableRW = counter

' Set a value for testing using manual locking

Dim variables As IDTSVariables90

Me.VariableDispenser.LockOneForRead("Variable", variables)

variables(0).Value = 5

variables.Unlock()

MyBase.PostExecute()

End Sub

End Class

|||

Hi Darren

Great Help!! thanks,

I got my work done.

Regards

Rahul Kumar,Software Engineer,India

sql

R/W access problem with var in script Component

Hi there

I have a a global variable for the package.

Now in script task of data flow I am trying to assign some value to it.

I have also defined same variable in Read/Write property of script task.

But at run time it gives me following error:

"The collection of variables locked for read and write access is not available outside of PostExecute."

How do we resolve this?

Thanks and Regards

Rahul Kumar

And in which overrided Sub are you trying to access this variable?

-Tom

|||

Hi Tom

I am doing all this in

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

|||

Try this:

Public Overloads Overrides Sub ProcessInput(ByVal inputID As Integer, ByVal buffer As PipelineBuffer)
If Buffer Is Nothing Then
Throw New ArgumentNullException("buffer")
End If
Dim vars As IDTSVariables90
Dim varServerName As IDTSVariable90
Dim varPackageName As IDTSVariable90

....

Try

...
Me.VariableDispenser.LockForRead("ServerName")

...
Me.VariableDispenser.GetVariables(vars)
varServerName = vars.Item(0) (0 because this was the first variable you've locked)

...

catch ....

|||

You can find lots of examples by just typing in msnsearch "using variable in script component"

like for example:

http://msdn2.microsoft.com/en-us/library/aa337079.aspx

-Tom

|||

Tom, your code sample is not actually for a Script Component, rather a full custom component.

Rahul, to be clear, when using the variable lock lists provide in the UI of the the Script Component you cannot access variables in the process input member (MyBuffer_ProcessInputRow). You can access read-only variables in the PreExecute and read-write variables in PostExecute

From the BOL link in Tom's second post -

Important:

The collection of ReadWriteVariables is only available in the PostExecute method to maximize performance and minimize the risk of locking conflicts.

If you do the locking yourself though you get more control. Try this sample code, but create the variables first if you try and run it.

Public Class ScriptMain

Inherits UserComponent

Private counter As Integer

Public Overrides Sub PreExecute()

' Initialise Counter from RO variable

counter = Me.Variables.VariableRO

MyBase.PreExecute()

End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

' Increment Counter

counter = counter + 1

' Use manual locking to set variable in Process Input

Dim variables As IDTSVariables90

Me.VariableDispenser.LockOneForRead("VariableRWManual", variables)

variables(0).Value = 125

variables.Unlock()

End Sub

Public Overrides Sub PostExecute()

' Store updated counter in RW variable

Me.Variables.VariableRW = counter

' Set a value for testing using manual locking

Dim variables As IDTSVariables90

Me.VariableDispenser.LockOneForRead("Variable", variables)

variables(0).Value = 5

variables.Unlock()

MyBase.PostExecute()

End Sub

End Class

|||

Hi Darren

Great Help!! thanks,

I got my work done.

Regards

Rahul Kumar,Software Engineer,India

Quick question on VB

Hey folks,

I am trying to run a batchfile or a external process ( exe ) from within a Script Task and read the process status returned by the process. Is there a sample VB script code that does this that you guys can share ? What modules do I have to Import ?

Thanks

-chiraj

This should do the trick. To test this, create a batch file in C:\temp called test.bat. Simply put "foobar" or something invalid in the batch file to create an errorlevel. Copy the code into a script task, run the package. It will show you the errorlevel in a message box.

Imports System

Imports System.Data

Imports System.Math

Imports System.Windows.Forms

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dim stat As Integer = ExecProc("C:\\temp\\test.bat")

MessageBox.Show("The exit status was : " + stat.ToString())

Dts.TaskResult = Dts.Results.Success

End Sub

Public Function ExecProc(ByVal Path As String) As Integer

Dim objProc As System.Diagnostics.Process

Dim status As Integer

Try

objProc = New System.Diagnostics.Process()

objProc.StartInfo.FileName = Path

objProc.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Normal

objProc.Start()

'Wait until the process passes back an exit code

objProc.WaitForExit()

status = objProc.ExitCode

Catch Ex As Exception

MessageBox.Show(Ex.Message)

Finally

'Release the objProc resources

objProc.Close()

End Try

Return status

End Function

End Class

HTH,

Kirk Haselden
Author "SQL Server Integration Services"

|||Thank you, Kirk.

Quick ? on Backup

Is there a way to run a T-SQL or scheduled task backup, to incorporate only
some tables? I want to do a backup of a DB, but exclude a table or two. Is
that possible? If so, where do I get the info on it?
Thanks.Hi,
Not directly.
Since Filegroup backup is possible you can create seperate file groups and
create the required tables to backup in the seperate file group. So as you
can backup the file group.
Thnks
Hari
MCDBA
"CQL User" <foo@.cqlcorp.com> wrote in message
news:OvZCa7UKEHA.1192@.TK2MSFTNGP11.phx.gbl...
> Is there a way to run a T-SQL or scheduled task backup, to incorporate
only
> some tables? I want to do a backup of a DB, but exclude a table or two.
Is
> that possible? If so, where do I get the info on it?
> Thanks.
>

Quick ? on Backup

Is there a way to run a T-SQL or scheduled task backup, to incorporate only
some tables? I want to do a backup of a DB, but exclude a table or two. Is
that possible? If so, where do I get the info on it?
Thanks.Hi,
Not directly.
Since Filegroup backup is possible you can create seperate file groups and
create the required tables to backup in the seperate file group. So as you
can backup the file group.
Thnks
Hari
MCDBA
"CQL User" <foo@.cqlcorp.com> wrote in message
news:OvZCa7UKEHA.1192@.TK2MSFTNGP11.phx.gbl...
> Is there a way to run a T-SQL or scheduled task backup, to incorporate
only
> some tables? I want to do a backup of a DB, but exclude a table or two.
Is
> that possible? If so, where do I get the info on it?
> Thanks.
>

Quick ? on Backup

Is there a way to run a T-SQL or scheduled task backup, to incorporate only
some tables? I want to do a backup of a DB, but exclude a table or two. Is
that possible? If so, where do I get the info on it?
Thanks.
Hi,
Not directly.
Since Filegroup backup is possible you can create seperate file groups and
create the required tables to backup in the seperate file group. So as you
can backup the file group.
Thnks
Hari
MCDBA
"CQL User" <foo@.cqlcorp.com> wrote in message
news:OvZCa7UKEHA.1192@.TK2MSFTNGP11.phx.gbl...
> Is there a way to run a T-SQL or scheduled task backup, to incorporate
only
> some tables? I want to do a backup of a DB, but exclude a table or two.
Is
> that possible? If so, where do I get the info on it?
> Thanks.
>