Pass second output of procedure to SSIS OLEDB Command

Question

I have a situation where I am using a dataflow task where I am populating data (Call SQL command) from an OLEDB source and inserting it at the destination using vdestination.

In the OLEDB source, I am calling a stored procedure which is returning the following columns in the second output set:

SELECT 
    ERROR_NUMBER() AS ErrorNumber
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_PROCEDURE() AS ErrorProcedure
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_MESSAGE() AS ErrorMessage; 

Now I want to redirect this second output column to the OLEDB command transformation in the dataflow task where I am calling one stored procedure for error logging.

Here are the list of questions:

1. How do I get the second result set of the OLEDB source and redirect to the OLEDB command transformation?

2. How do I pass parameters to a stored procedure in the OLEDB command transformation?

3. I also want to redirect the error output for all rows where all remaining rows can be logged.

Answer 1

I resolved this using some trick with condition split and temp tables in procedure.