title: SSIS 2008 - How to log container error message on transaction rollback
body: <p>I've a new package containing a sequence container with a bunch of sql and data flow tasks inside. The intended behaviour is to create a transactional process over the container: if any task fail, then a rollback needs to be performed and the <strong>error message</strong> must be <strong>logged for analysis</strong>.</p>
  
  <p>I set <em>TransactionOption</em> property of different objects as follow: </p>
  
  <ol>
  <li>Package level : Supported</li>
  <li>Container : Required</li>
  <li>Each SQL or Data Flow Task : Supported</li>
  <li>Enabled Microsoft Distributed Transaction Coordinator</li>
  </ol>
  
  <p>I've also introduced a duplicate primary key error.. and effectively works as expected: the rollback works fine.</p>
  
  <p>The next step would be log into a SQL Server table the duplicate primary key error message. My questions are: </p>
  
  <ol>
  <li>Which is the most appropiate place to catch the error message? OnError event of the containt? The PostExecute event of the container? Others? </li>
  <li>On my investigation.. I'm loading the error message into a SSIS package variable. How can I pass it through a stored procedure input parameter? </li>
  </ol>
  
  <p>Any comments/suggestions will be welcome. </p>
  
  <p>Thanks in advance</p>