error handling in OLEDB source in data flow

I am trying to execute a SP like below in OLEDB source in data flow... and this statement include the insert stament ( row by row transaction).. I would like to creat an error hadling logic so that if the trasaction fail to insert the row then ignore that particular row then, move to the next row without stopping the whole process.. how can i do this?

exec usp_Inert_Registration_Episodes_Assessments

@.Unique_ID=?,

@.Gender_Cd=?,

@.Birth_Date=?,

@.Race_Ind=?,

@.Ethnicity_Cd=?,

@.Registration_Dt=? ,

--

--@.Object_Key

Recently i was working on similar thing.

To do this in SSIS the following article will be handy

1) http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/03/03/126.aspx

2) http://www.sqlis.com/55.aspx

3) http://www.sqlis.com/58.aspx

4) http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-Execute-SQL-Task-into-an-object-variable-_2D00_-Shred-it-with-a-Foreach-loop.aspx?CommentPosted=true#commentmessage

Regarding error handling thats very much possible

Say for instance you getting data through OLE DB source. Then in the editor window, you can find "Error Output". here you can specify what to do with your error set. You can "Fail the component"/ ' Re Direct the row / ' Ignore Failure'

In case you want to redirect the row, then by setting the proper output to the error(Red Line) you can do as you want.

I hope this solves your problem.

|||

thanks,.. but

problem is my oledb source is a SP that execute insert statment.. so there are no output columns that i can map it to are available.. how can i solve this problem?

|||Why are you using an OLE DB source to insert data via a stored procedure? An Execute SQL task in the control flow would be a much better idea.|||

I agree with Phil

1) If you need to Insert with same datbase, use Execute SQl task

2) If in different database, use data flow task in which you can specify the source and destination.

secondly if you have some queries running bnefore running and this all is happening in a sproc then you can run that Sproc using Execute SQL task- Simple!!!

|||

thanks.. but if i use SQLTASK , i don;t have the option to use "Error Output" in data flow which i can specify what to do with the error row. in my case skip that row ( put that row in the error log table) and go to the next row..

how to handle the error row in the SQLTask if i want to redirect that row and go to the next row without stopping the whole process?

|||You can use an OLE DB Command component in the data flow to call the procedure. That will let you redirect error rows. However, you still need a source component to feed rows to the OLE DB Command.

Where were you planning on getting the data to feed into the procedure?

|||

Thanks jwelch -

"That will let you redirect error rows"

--Do i have to do something inside OLEDB command to be able to do that? or is it going to automatically redirect the row and move to the next row?

|||

If you drag and drop the red output arrow from the OLEDB command to another component, it will prompt you to configure it.

|||

ok.. in the data flow, i am using OLE DB source ( SQL command variable) and pass them to the OLE DB command to execute the insert statment ( by passing variables ).. I changed the error output to redirect rows to skip the row which didn't get inserted( because of an error) and move to the next row( it works)...

I drag and drop the red output arrow from the OLEDB command to another OLE DB command which will execute the insert statment to insert the failed row to Error_Log table...however, even though the row which has an error got skipped , that row didn;t get inserted to the error table.. i am not sure what i am doing wrong..

i am trying to insert the error row with the error description to a table.. what is the best way to do this?

|||

When you run it in the debugger, is a row count displayed on the red arrow?

|||

do i have to put a data viewer to be able to see it? i dont see it

but i see the color of insert to error table OLEDB command task turn to green for a sec

|||

If you are not seeing a number, it sounds like no rows are being sent to the error output. You can add a data viewer to confirm this. That indicates that the error output is not configured properly, or that the OLE DB Command is not failing on any rows.

|||but only one row out of two got inserted.. how to insert that failed row to a table?|||

safddddddddddddddddddddd wrote:

but only one row out of two got inserted.. how to insert that failed row to a table?

Hook the error output to a second OLE DB Destination.