Hi all, Here is my problem: I have a package which loads a dimension table. Within this package is a sequence container in which the actual dimension load process occurs and the TransactionOption for this sequence container is 'Required'. This package runs on a staging server and then loads and updates data in the dimension table. We have a number of different environments and in some cases, the warehouse database is on a separate server than the ssis packages, and a linked server is set up, and in others everything is on the same server. In all environments, in the following scenario, the error occurs Within the sequence container are five tasks in the following order Execute SQL Task which gets the max sid from the dimension table - TransactionOption = NotSupported Execute SQL Task which truncates a changed data table in a database on the same server as the ssis package - TransactionOption = NotSupported DataFlow Task which loads the dimension table with new rows and also sends changed rows to the changed data table for subsequent processing - TransactionOption = Supported Execute SQL Task which processes the changed data by updating the dimension table from the changed data table - TransactionOption = Supported Execute SQL Task which adds NA and Unknown members to the dimension if they don't already exist - TransactionOption = Supported The problem occurs at step 3. Nine times out of ten it will run without error, however if there are new rows and changed rows at the same time, I get the following error: Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "This operation conflicts with another pending operation on this transaction. The operation failed.". I've checked the msdtc setup and it appears correct. The DataFlow tasks have an AccessMode = 'OpenRowset Using FastLoad' and when I change this to 'OpenRowset' the error does not occur. Any thoughts I why FastLoad would cause this package to fail? <edit>Upon further investigation, it appears that the problem only exists in environments where all the databases are on the same server</edit> Thanks, -Mark

I believe this issue is purely the linked server related. Even though you have not listed any details on your environment I believe KB 949687 is applicable to your case which is CU7 to SP2 of SQL Server 2005: http://support.microsoft.com/kb/949687 Obtain this cumulative update and apply if the above is related.Arthur My Blog

We are running 2008R2 so that patch would not appear to apply. Turns out it the value for DefaultBufferMaxRows for the data flow in step 3 was set to 1 and bumping it up to the default value resolved the issue. That still doesn't explain for me why the error occurred, but it does solve the problem.

We are running 2008R2 so that patch would not appear to apply. Turns out it the value for DefaultBufferMaxRows for the data flow in step 3 was set to 1 and bumping it up to the default value resolved the issue. That still doesn't explain for me why the error occurred, but it does solve the problem. CAn you still details a bit more about your pacakge, assume your tables which are tired to be updated via the sequence container where accessed from somewhere outisde, i.e via some other tasks which may be outisde the SqContiner1, whcih have mentioned as the SQCont1 has started a new trnasaction and the DFT inisde it are supported so have a a tran2 statrted inside the pcakage, so your package is running under a tran1 and if table is accessed inside tran1 then this eror is likely to arise. Also that would explain why 9 times out 10 it passes as majority time the tran 1 load completes before Seqcontainer starts.Abhinav