OLE DB Command Stage: Capturing Rejects

Hello group, I have a question regarding the OLE DB Command Stage. Currently, I am reviewing a Data Flow that runs in production. This Data Flow Inserts to the various dimension tables in our warehouse. For a particular dimension table, the flow is like this:

Read Source records for Product combinations
LookUp Product combinations against the current dimProduct table (cached in memory)
Rows not found are then subjected to another LookUp on the dimProduct table (not cached). This is to find any rows inserted during the current run
Rows not found are then Inserted to dimProduct using a Stored Procedure invoked by an OLE DB Command
Successful Inserts then continue on, Rejected Inserts should be captured to a Flat File on our server for review.

Apparently, this last step has never been successful at capturing Rejects. Obviously, we would want to review these records to find the reason for failure. We get an empty file.



Currently, in the Stored Procedure we are using logic like this:





IF @PRODUCTCOUNT <> 0

BEGIN

RAISERROR ('DUPLICATE PRODUCT!', 10, 1)

RETURN

END



Questions:

Is the RAISERROR command going to give us Output?
Can we implement the OUTPUT command in our Proc invocation?
I have not found any documentation that says the OLE DB Command Stage supports Error logging (Although columns are available to be added in the Input/Output columns tab??)
Should we be using another Stage to accomplish this?

Any thoughts are welcome, thanks for your time!



rg

IF @PRODUCTCOUNT <> 0

BEGIN

RAISERROR ('DUPLICATE PRODUCT!', 10, 1)

RETURN

END