title: SSIS error with OLE DB command
body: <p>I have an SSIS Package which I execute manually for testing. When I do so, an error occurs (see pic below). But first let me explain what happens in the package: There is just the dataflow itself. In there I query a view (OLE DB Source) which will provide me the parameters I will need for the next step. Then the arrow points to the OLE DB Command where I EXEC a stored procedure on the local DB server and hand over the parameters as well. 
  This SP is my wrapper where 3 main things happen: 1. SET XACT_ABORT ON; (we will need this for distributed transaction later on) 2. Call a helper SP which will create a new Synonym to my Remote Target table. (this creates a synonym like MY_REMOTE_TABLE, when it is created I can query it maunally with no problem) 3. Call the helper SP which will perform the main task.
  This helper SP has a BEGIN DISTRIBUTED TRANSACTION. In there I insert into MY_REMOTE_TABLE some records from my local DB. Now I placed a log entry directly before and directly after the BEGIN DISTRIBUTED TRANSACTION, but in my log there is only the entry which I print before the transaction begins. So it looks like it fails as soon as It opens up the distributed transaction. 
  The pic below shows the error I get from SSIS, but I have no idea what to do about it. Any idea is very much appreciated!</p>
  
  <p><img src="https://i.stack.imgur.com/7Isl0.jpg" alt="SSIS Error MSG">  </p>
  
  <p>Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E14.  An OLE DB record is available.  Source: "Microsoft OLE DB Provider for SQL Server"  Hresult: 0x80040E14  Description: "The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction"
  Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Command Input" (140)" failed because error code 0xC020906E occurred, and the error row disposition on "input "OLE DB Command Input" (140)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. 
  Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "exec Run_My_Wrapper_SP" (135)" failed with error code 0xC0209029 while processing input "OLE DB Command Input" (140). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.</p>
  
  <pre><code>BEGIN TRY
    BEGIN DISTRIBUTED TRANSACTION 
    -- do my stuff here
  
    IF (@@TRANCOUNT &gt; 0 AND XACT_STATE() = 1)
    BEGIN
      COMMIT TRANSACTION 
    END
  
    IF @Output_on = 1
      EXEC sp_write_to_log 'End of DISTRIBUTED TRANSACTION!';
  
  END TRY
  BEGIN CATCH
    IF (@@TRANCOUNT &gt; 0 AND XACT_STATE() = -1)        
      ROLLBACK TRANSACTION
      INSERT INTO my_error_log (Time, Source, MSG, MSG_NR, Line_Nr) 
      VALUES (GETDATE(), ERROR_PROCEDURE() , ERROR_MESSAGE(), ERROR_NUMBER(), ERROR_LINE());
  
      IF @Output_on = 1
        EXEC sp_write_to_log 'Error in DISTRIBUTED TRANSACTION!';
  
  END CATCH;
  </code></pre>