March 27, 2012 at 7:02 pm
We have a Parent SSIS package that calls multiple children packages. The Parent package consists of a script task and a sequence container. The sequence container contains another sequence container which contains the execute package tasks. Each of the execute package tasks truncate a table then repopulate the same table. All packages, tasks and sequence containers have TransactionOption = Supported (never required) and IsolationLevel = Serialized. We have set delay validation on all connections, packages, tasks and sequence containers to true. When we run the parent package via BIDS or DTEXEC it works fine. When we run the parent package via a Windows Service, the script task executes, the first child package executes and the second child package hangs. The Execute SQL Task that truncates the table runs successfully, howerver the data flow that repopulates the table hangs. We turned on logging and see
ExternalRequest_post: 'ITransactionJoin::JoinTransaction succeeded'. The external request has completed.
Validating
This tells me that the package is hanging on the validation event. However, transactions are supported and not required so where did the ITransactionJoin come from? My suspicion is that the execute sql task was blocking the process but, we disabled that task and had the same result. MSDTC is not running on the SSIS box. We are using SQL Server 2008 R2. Any thoughts?
BTW...if we let the process run it eventually finishes in about 8 hours. No errors are returned.
March 28, 2012 at 6:04 pm
Assuming that for whatever reason there is an open transaction that your packages are joining, what happens when you try explicitly denying transactions (NotSupported)?
Another suggestion is to try turning off ValidateExternalMetadata.
I have noted some issues in the past myself, particularly around child packages and transactions, but since you do not appear to require transactions I'm not sure how useful the information will be.
Here you go anyway (directly copied from my personal notes):
1. Data destinations by default verify themselves against the database tables by acquiring a schema lock which isn't released until the transaction is ended. This can cause problems with the simplest of packages when run in transactions with other packages. You can however prevent it by toggling the ValidateExternalMetadata value to false. If the package stalls on validation the first time it is executed within a group this may be the cause.
2. There is a bug in SQL Server that prevents the committing of a buffer with more than about 8 thousand rows in the situation where the package is in a transaction, the destination table is empty, the database uses simple logging, bulk inserts (fast load) are used. The issue can be avoided by inserting a single row prior to the DFT or a patch can be applied (comes in CU3?).
3. It is possible to achieve a situation where a package will inexplicably fail with a cryptic message about transactions. This was found to occur in 2 fact packages but may possibly occur in dimensions. The problem arises in the update architecture (as opposed to truncate-reload). The SQL statement that SELECTS the pre-existing data from the Fact table does not complete before the bulk insert(fast load) insert statement attempts to execute. In this situation it appears the transaction can deadlock or be aborted (causing the error) depending on configuration. This has now also been seen in aggregation packages. To avoid this issue you can put in place a blocking transformation either just before the insert or just after the read. Alternatively you can turn off the fast load. Depending on the size of your dataset one or the other may be a better option. Test and see. I suspect the blocking SRT will be faster in most if not all cases.
4. Recent Addition: you may find that similar to note 1 the package hangs on validation phase the second time it is executed within a group (it completed successfully the first time). This has occurred on some dimensions where the dimension source component before the merge join could not Validate External Metadata the second time around. Set this property to false to resolve.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply