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 |
|
saidwarak01
Starting Member
1 Post |
 Posted - 2009-07-02 : 18:08:48
|
| Hi all,I have a ssis package and it contains a for each loop container and it loops through each and every csv file and loads the csv file data into tables. Now I want this package to be transactional based.Without transactional based option it is running fine and when I add the transaction option to the package and also to individual tasks then it is giving error. The transactionoption for my package is set to required and the order of my package items are as follows:1. For each loop container: I defined two variables the csvfilepath and csvfilespec which defines the folder path and type of files it need to extract. Inside the for each loop container I have 2,3,4,5 items. The transactionoption is set to supported.2. Execute sql task1: Which inserts the the record with job details like jobno , filename into job table . This filename is coming from the foreachloop container as I defined a vairable which captures the filename it is processing and this execute sql task has ole db connection. The transactionoption is set to supported.3. Execute sql task2: This task helps in retrieving the max jobno from job table and stores in a variable. The transactionoption is set to supported.4. Data flow task: This task has one flat file source, derived column transformation(to create a new column and max job no will be placed in this one and then the data is sent to ole db destination) and ole db destination. The target table is sql server and I used ole db connection. The transactionoption is set to supported.5. Executte sql task3: This task has selectstatment as 'select 1/0' The transactionoption is set to supported.Now I want to have transactional system for my package. As I know that my last sql task going to fail I want the whole package to get rollback. But I am getting the following error: [Execute SQL Task] Error: Failed to acquire connection "xxxx". Connection may not be configured correctly or you may not have the right permissions on this connection. Task Execute SQL Task1 failed[Connection manager "xxx" Error: 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.". Please help me in resolving this issue and it is very urgent to complete this task. I request moderators to please help me urgently.Your help is appreciated.Thanks |
|
|
|
|
|
Transactional based SSIS package is failed