Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jocorbett
Starting Member
1 Post |
 Posted - 2007-11-15 : 14:11:36
|
| Hi,I am new to SSIS but have been putting together a relatively complex ETL over the last few weeks. However, I have been stuck on the problem below for the last 4 days and to save my sanity, I would appreciate any help or advice.Thanks in advance,jcI have a 'Control_Upload' package which contains 2 transaction containers in series. Being in series, the transactions should never overlap. The first transaction container exists within a For Each container and contains several 'Execute SQL' and 'Data Flow' tasks. I have set the 'TransactionOption' on the Transaction container to 'Required' and the tasks within the container are set to a combination of 'Supported' and 'NotSupported'. I have set the IsolationLevel on all 'Supported' tasks to 'ReadUncommited'.On completion, the second transaction is executed.The second transaction container also exists within a For Each container and contains 2 'Execute SQL' tasks and 3 'Execute Package' tasks. The invoked packages also contain a ForEach Loop and several 'Execute SQL' and 'Data Flow' tasks. Again, I have set the 'TransactionOption' on the Transaction container in 'Control_Upload' to 'Required' and the tasks within the container are set to a combination of 'Supported' and 'NotSupported'. I have set the IsolationLevel on all 'Supported' tasks to 'ReadUncommited'.I am experiencing 2 different errors which the SSIS configuration described above. The first error relates to the first transaction. SSIS executes a number of the 'Execute SQL' and 'Data Flow' tasks which are 'Supported' by the transaction, however, it hangs indefinitely (without any warnings or errors) when it tries to execute the final 'Data Flow' task. The initial tasks execute as expected and the transaction completes if I disable the 'hanging' task. When writing data to tables within all 'Data Flow' tasks, I have the 'Data Access Mode' set to 'Table or view - fast load' and have unchecked the 'Table lock'. For this reason, I do not understand why I seem to be experiencing a potential contention lock!?!The second error relates to the second transaction. The 3 'Execute Packages' are 'Supported' in the transaction. The first 2 seem to execute successfully but the 3rd returns the following error message when executing one of the 'Data Flow' tasks within the 3rd package.'SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occured. Error code: 0x80004005. And OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "This operation conflicts with another pending operation on this transaction. The operation failed."'. However, the 3 'Execute Package' tasks are connected in series and like the previous transaction, I have the 'Data Access Mode' set to 'Table or view - fast load' and have unchecked the 'Table lock' within all DB destination components. For this reason, I do not understand why I seem to be experiencing a potential contention lock!?!Any thoughts would be greatly appreciated.Sorry for the length and apologies in advance for my overuse of '''''' !!! |
|
|
|
|
|
|
|
Transactions in SSIS