asked on

What's the SSIS equivalent or Err.Raise?

Hi all

I've got a OLE DB Source / expression to make sure if total debits equal total credits.
If they match, good to go.
If they don't match, I'd like to force execution to go to my package error handler routine.

Question: What's the SSIS equivalent or Err.Raise

TIA
Jim

ASKER CERTIFIED SOLUTION

Jagdish Devaku

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PedroCGD

Hi!
In SSIS you have EventHandler tab to control the errors!
helpd?
Cheers!

Jim Horn

ASKER

>please click on the below link for info on Error Handling in SQL Server...
>TRY&CATCH and ERROR Handling With RAISEERROR Function...
My question is for SSIS, and not T-SQL.

>In SSIS you have EventHandler tab to control the errors!
Yes, and in my question body is the phrase 'I'd like to force execution to go to my package error handler routine. ', so that is already known. This question pertains to what component/script will send code execution there, short of intentionally coding an error.

SOLUTION

PedroCGD

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Horn

ASKER

Getting there. Attached is the code in my data flow / script component (Destination).

The only problem I'm having now (a noob one I'm sure) is that every instance of dts has a blue squiggle line below it, and when mousing over it the yellow Intellisense says 'Name 'Dts' is not declared'.

Option Strict Off
 
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.IO
Imports Microsoft.SqlServer.Dts
 
 
Public Class ScriptMain
    Inherits UserComponent
 
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
 
        Dim sMessage As String
 
        If Row.Match = False Then
            sMessage = "Debits are " + CStr(Row.debits) + ", and credits are " + CStr(Row.credits) + ", difference is " + CStr(Row.debits - Row.credits)
 
            'Throw a 'Debits do not equal credits' error, and move code execution to error handler. 
            Dts.Events.FireInformation(0, "", sMessage, "", 0, True)
 
            Dts.TaskResult = Dts.Results.Success
        End If
 
    End Sub
End Class

Open in new window

PedroCGD

dear friend,
sorry for the delay... I was very busy!!
Do you still help in this issue?!
Cheers!!

Jim Horn

ASKER

Guys

Nothing is working here, and I've decided that this approach has too many limitations, so I'm going to 'roll my own' validations inside a SP, which returns a value, and then test the value within the SSIS package.

Will lower points to 250 and split in appreciation of leading me to this solution.

Thanks.
Jim

PedroCGD

Use inside an SP an output parameter that return 0 when is OK and another number if an error occur!
If this SP is critical use transactions!
Cheers!