Thursday, March 29, 2012

ETL : rows with Errors

I'm using a "Execute SQL Task" to run a stored procedure to populate a Fact table in our dimensional datawarehouse. There is one row which violates a foreign key constraint, which causes the entire task to fail so zero rows are loaded. Is there any way to grab the offending row and send it off to some holding ground and go ahead and load the rest of the rows.

I'm using Execute SQL Task mostly because I am very comfortable with writing SQL whereas the rest of SSIS is a bit unfamiliar to me, but I'm guessing that to handle error rows I might have to change to a different kind of task ?



The best way to do this is to use a lookup in the dataflow to determine if the incoming record violates foreign key rules.

As far as your stored procedure goes, head over to the Transact-SQL forum for help with that.

No comments:

Post a Comment