Problem with SSIS Transaction...Transaction Scope

Problem with SSIS Transaction...Transaction Scope

• Paris Baughman

  I am experiencing a similar problem with transactions myself. I cannot seem to break the Enlisting of a sibling in an existing transaction.

  My situation is:

  BatchManager Package

   - Trigger the starting of the batch job (Container - should use a single transaction and then commit)

   - Call a child package (NotSupported - child package requires a transaction in hopes that it is atomic)

   - Close batch job and log status (Container - should use a single transaction and then commit)

   - Send any notifications (Container - should use a single transaction and then commit)

  What I am seeing is the when the child package has an Exception thrown (even though I trap it and indicate success) the closing of the batch job fails as it is attempting to enlist in a closed DTC transaction.

  Any thoughs and does SP1 have any resolution to this

  NOTE: I am using Logging to the same SQL database on the same connection, could this be a contributing factor

  Fred

  
  

• Robert Reister

  They are in parallel. Yes, I also noticed the parallel issue, but since they are in the loop and it is made that only one of those sequence containers will be executed for each record, using precedence flow control. May be I will try that in different way.
  
  

• devexpert

  Hi FredH, sorry, I was away from internet for a while, can you tell me more detail how you solved your problem. I will try to follow your solution in my problem.

  rgds,

  KyawAM

  
  
  

• Don Tran

  Just for simplicity of configuration so we don't have multiple configurations for the exact same database. I only make the change to the connection string since I get the error with enlistment if it stays the same.

  I sent the sample failing package to Jamie and he could not reproduce the error but he was running SP1 and so this may actually be fixed there.

  I will be submitting a bug with the sample package to ensure it is logged even though it might be fixed in SP1. I could e-mail you the sample if you like.

  
  

• brob

  I think this is a bug but not 100% sure...

  I discovered why I was getting the Enlistment issue with Distributed Transactions when I should not have been. I started to use Indirect Configurations for the data connection between packages so they would simply reuse the same information. It turns out that the connection string being set from the configuration files was the problem!

  As above, the package flow was:

  Parent Package (Transaction: NotSupported; OleDb Connection)
  1) Initialize the Batch Job Sequence Container (Transaction: NotSupported)
  2) Run Child Package (Transaction: NotSupported BUT required in the package itself)
  3) Close Batch Job (Transaction: NotSupported)
  4) Send notifications that were logged with attachments (Transaction: NotSupported)

  So, the parent package used a connection configuration file (.dtsConfig) and the child package was using the exact same configuration file to set the same connection in the child package. With this I would get an enlistment error with the transaction on Step 3 & 4 when it should not have.

  I was able to correct this by not using the configuration file on the child package (inline configured for testing) OR by using the configuration and changing the ConnectionString manually through script at the start of the package (Script Task). The change I made was simply assigning the "Application Name" property of the connection string so it makes it easier to determine what connections belong to what jobs.

  Could someone from MS confirm this behavior

  
  

• SHakeelGhauri77

  What is the transaction setting of the package Also how do you have the transaction settings for each container

  
  

• Tim Wong

  Are each of the sequence containers in the loop sequencial or parallel You might try to sequence them to see if that causes success. This may not correct your issue but would give a bit more insight.

  
  

• 52179apb

  Thanks to Jamie, I got my problem solved using his RetainSameConnection solution. DTC is really unpredictable in SSIS. I could solve this because I am using the transaction in only one connection. I am wondering if I were using different connections for my transaction, then the problem will still be there...
  
  Thanks also to FredH...
  rgds,
  KyawAM
  
  
  

• Randal Greene

  Fred, is there a reason why you don't have a second configuration for the different setting you want on the child package
  
  

• hairunizam

  Sure, are you using configuration files to set the connection string property of the child package If so, you can work around this transaction problem by simply adding a little script at the top of the child package that alters the ConnectionString property of the connection(s).

  Since it is a benefit anyway, I add the "Application Name" connection string property to the string with the package name so that it causes the transactions to function properly AND I can tell when connections are used for which batch job in Activity Manager in SQL.

  I can send you a sample package that uses AdventureWorks database to highlight the breaking behavior and work around if your e-mail is correct. I am going to log this with MS so they can address it.

  
  

• AnnabelP

  The package transaction setting was "not supported"

  Next level container (Loop Each ADO records) was also "not supported"

  Inside the loop, there are about 5 sequence containers which will take into action depending on certain condition is met with each record. I wanted those sequence containers, each with a separate transaction scope. So I gave them the transaction setting "required" and the transaction for all other containers and tasks inside them were set "supported". I tried different type for the transaction but still is not working.

  BTW, I am using the same Db connection for the transactions inside the sequence containers since they are connecting to the same DB.

  rgds,

  KyawAM

  
  
  

• M Eslinger

  Hi Fred, I am also using SP1, I think they haven't yet solved the issue. Also I am not using package level transaction. Its inside one same package using differrent sequence containers for each transaction within the loop container. I even tried using script to close/dispose the connection after each transaction but still having the error.

  
  
  
• Problem with SSIS Transaction...Transaction Scope