title: SSIS: Enabling transactions causes database connection to fail
body: <p>I have a package that I'm using to load records from a CSV file into a table. It has three elements in the control flow:</p>
  
  <ul>
  <li>Truncate table</li>
  <li>Load File into Table</li>
  <li>Verify that there are records on the table after the load or raise an error</li>
  </ul>
  
  <p>The idea is to have a single transaction on the package, so if the load of elements fails or the file was empty then the transaction is rolled back and the table isn't truncated.</p>
  
  <p>To enable the transaction I just go to the package properties and set <strong>TransactionOption=Required</strong>, then I just try to execute the package and get this error while trying to execute the first element (The SQL task that tries to truncate the table):</p>
  
  <blockquote>
    <p>[Execute SQL Task] Error: Failed to acquire connection "Database
    Connection". Connection may not be configured correctly or you may not
    have the right permissions on this connection.</p>
  </blockquote>
  
  <p>If I just go back and change the TransactionOption property of the package to the default (Supported) then the package executes correctly but if there's an error there's no rollback.</p>
  
  <p>I am using ADO.NET to connect to a SQL Server DB.</p>
  
  <p>Any idea of what am I doing wrong? Is this the correct way to use transactions or am I missing something?</p>
  
  <p>Thanks!</p>