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.

All Forums  SQL Server 2005 Forums  SSIS and Import/Export (2005)  1 Transaction in 1 Package VS2008

Author  Topic  maurowp Starting Member 4 Posts Posted - 2010-06-08 : 03:36:06 Hi all, I'm trying to implement a single transaction in a single package. This package has 2 DataFlowTasks. Inside each of the DataFlowTask there is a simple operation: OLEDB Source -> ScriptComponent -> OLEDB Destination that read data from a table in a database and write other rows in the same table. I created this package without caring about transaction, all TransactionOption=Supported. Everthing worked fine, package run successfully. Problems started when I tried to implement only 1 transaction. Following instructions, I set TransactionOption=Required for the package and TransactionOption=Supported for the two DataFlowTask inside it. More options set: ValidateExternalMetadata=False for the two OLEDB destinations ProtectionLevel=EncryptSensitiveWithUserKey for the project EnablePackageConfigurations for Collections for project When I launch it, I have this error: Error: 0xC001402C at Package1, Connection manager "myconn": The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D025 "The partner transaction manager has disabled its support for remote/network transactions.". Error: 0xC0202009 at Package1, Connection manager "myconn": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8004D025. Error: 0xC020801C at Data Flow Task, OLE DB Source [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "STAGING" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. Error: 0xC004701A at Data Flow Task, SSIS.Pipeline: component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC020801C. Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "component "OLE DB Destination" (19)" wrote 0 rows. Information: 0x40043009 at Data Flow Task, SSIS.Pipeline: Cleanup phase is beginning. Task failed: Data Flow Task Any suggestion will be appreciated!!! Thanks in advance Mauro