SSIS how to write to log table without being rolled back by a transaction failure?

Question

When processing a batch of records, if any of them fail business rule validations the entire set of inserted data is rolled back for data integrity. During this process I need to log such errors to a table in the database - which shouldnt be rolled back when the overall transaction is.

What is the best way to do this in SSIS? Different connection for when logging, or can I somehow 'ignore' the active transaction when writing to the log table?

Answer 1

It looks like I can set containers to Not Supported... http://blogs.conchango.com/jamiethomson/archive/2004/12/14/SSIS_3A00_-Package-transactions.aspx

Answer 2

Have you implemented the SSIS native logging yet? Go to the SSIS menu and select Logging. Use a separate transaction and either log it to a database or a flat file. I log it to a database and truncate it every load, so that way I have that run's logs available in case of failure.