title: How to get detailed error from CLR Stored Procedure called in DTC transaction from SSIS Package
body: <p>Whenever an exception is thrown in CLR stored procedure called from a SSIS package, within a DTC transaction , the error returned to client (SSIS package) relates to DTC rather than the underlying exception.</p>
  
  <p>Is it possible to have information for the underlying error returned to client? </p>
  
  <p>Note: When stored procedure is run from SQL Server Management Studio, outside of distributed transaction, detailed information for underlying error is returned.</p>
  
  <blockquote>
    <p>Error: Executing the query "StoredProcedure” failed with the following
    error: “The Microsoft Distributed Transaction Coordinator (MS DTC) has
    cancelled the distributed transaction”. Possible reasons: Problems
    with the query, “ResultSet” property not set correctly, parameters not
    set correctly, or connection not established correctly.</p>
  </blockquote>
  
  <p>All code running on a single SQL Server 2008 instance.</p>
  
  <pre><code>SSIS Package
  ---&gt; Sequence Container (TransactionOption = Required )
  ---&gt; Execute SQL Task (ADO.NET Connection Manager, SQLClient DataProvider)
  ---&gt; SQL Server CLR Stored Procedure (No exception handling code)
  ---&gt; TransactionScope(TransactionScopeOption.Required)
  </code></pre>
  
  <p><strong>Code Which Reproduces Issue</strong></p>
  
  <p>The following code illustrates the issue but differs from the scenario described in the title in that client is a C# console app rather than a SSIS package</p>
  
  <p><strong>CLR Stored Procedure</strong></p>
  
  <pre><code>using System;
  using System.Transactions;
  
  public partial class StoredProcedures
  {
      [Microsoft.SqlServer.Server.SqlProcedure]
      public static void ExceptionStoredProcedure()
      {
          using (TransactionScope scope = new TransactionScope())
          {
              throw new Exception(@"Test exception thrown from ""ExceptionStoredProcedure""");
          }
      }
  };
  </code></pre>
  
  <p><strong>C# Console App Client</strong></p>
  
  <pre><code>using System;
  using System.Data.SqlClient;
  using System.Transactions;
  
  namespace SQLCLRTester
  {
      class Program
      {
          static void Main(string[] args)
          {
              try
              {
                  using (TransactionScope scope = new TransactionScope())
                  {
                      string connectionString = "Data Source=.; Initial Catalog=Experiments; Integrated Security=Yes";
  
                      using (SqlConnection noOpConnection = new     SqlConnection(connectionString))
                      {
                          noOpConnection.Open();
  
                          using (SqlConnection connection = new SqlConnection(connectionString))
                          {
                              SqlCommand command =
                                  new SqlCommand()
                                  {
                                      CommandText = "ExceptionStoredProcedure",
                                      CommandType = System.Data.CommandType.StoredProcedure,
                                      Connection = connection
                                  };
                              connection.Open();
                              command.ExecuteNonQuery();
                          }
  
                      } scope.Complete();
                  }
              }
              catch (Exception ex)
              {
                  Console.WriteLine("Exception caught: {0}", ex.Message);
              }
          }
  
      }
  }
  </code></pre>
  
  <p><strong>Message Printed By Client</strong></p>
  
  <p>Exception caught:  The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction</p>
  
  <p>Notes:</p>
  
  <ol>
  <li>InnerException is null</li>
  <li>If the transaction scope in either the C#
  console app or in the CLR stored procedure is removed then a DTC
  transaction is not created and the information for underlying error
  is returned .</li>
  </ol>