Hi all,I am having no end of trouble with transactions in the package which i am building. I now just want to go back to basics and see if someone can tell me where i should set specific transaction options.Firstly, my package runs a for each loop which loops through a directory of directories. In each of the sub directories there are 2 files. The first steps in the loop are to check if a folder has been processed previously, if so then it moves it to a specified directory. The reason that this is done first is that i cannot move the directory whilst it is being read in the foreach loop, so i pass the path to the next iteration of the loop. There is another file system move directory task outwith the foreach loop to deal with the last directory.Once this has been done, i parse the file name of the xls file within the directory to get a serial number which is assigned to a variable.The next step is where i envisage that the transactions should be implemented:I have a sequence container which contains 2 data flow tasks to be run in parallel. each of these reads data from a seperate work sheet in the xls file. and writes it to a database table. Each dataflow task consists of an excel source task, derived column task, look up task (used to derive an ID from the serial number stored in the variable), and an oledb destination.Upon completion, if the sequence container fails i want to set the destination folder path to the qurantine location. If it succeeds i want to copy the csv file contained in the same directory to a seperate location and then set the out put folder to the archive location.What i need to know is where do i set the transaction option if i want to roll back the data that has been inserted into the database if either data flow task fails?Please somebody help, as this is not working at all.Many thanks,Grant

On the Sequence Container set the Transaction to Required. Then on the 2 data flow tasks set the option to Supported. If either fail it will roll back.

Thank you. That has now confirmed that i am not going mad by thinking it was that easy. I have done this and it falls over in both the development environment and the test deployment.I am currently getting the following error message when i run it in the dev environment:Error: The AcquireConnection method call to the connection manager "<name here>" failed with error code 0xC0202009Any further suggestions are welcomed.Thanks for the prompt responseGrant

The AcquireConnection error is a pretty vague error in my experience. It usually has nothing to do with what's actually wrong with your package. It's very frustrating to debug at times. :) Have you had a chance to run the package from Visual Studio? By stepping through the package you may find out why it isn't processing all of your folders. It sounds like it's eating the error somewhere... Check out the Output window in the IDE. I get a lot of useful information there. Also if you think it might be a data problem put a data viewer between one of the shapes inside of your data flow task. Just double click one of your green arrows, and you'll see the tab for data viewers.

vague is a fairly good description of it :)In BIDS i can run the package fine until i set the transaction option on my sequence container to required. It is at this point i start to get complaints from the package. I have tried setting data viewers prior to my OLE DB destination tasks but it skips by them and hightlights the destinations in red.It seems that the transaction is the change that causes the problem. I cannot see however why this may be the case. Your input is much appreciated.Cheers,Grant

Is your Sequence Container inside another container that has the transaction level set to required? Or does the package have the transaction level set to required? Only the top level object should be set to required, and any child objects should be set to supported. So change your Sequence Container to supported, and see if that helps.

Its funny you should mention tah. At present nothing above the sequence container is set to required. That is the For each loop and the package itself.I had tried earlier to set the main package to required and set some of the tasks below that to supported, and the package worked fine. My only concern with this method is that i don't want to roll back each and ever loop iteration. Am i off the mark in the way that i am thinking that the transactions perform there roll backs?If I set Required at the package level and only the insert tasks to supported, does that mean that they are the only tasks to get rolled back in the specific iteration of the loop? or will the whole looping structure be looped back?Thanks for this, think (/hope) we may be getting somewhereGrant

Hopefully I'm not leading you down the wrong path Grant. It's difficult to diagnose these things via a forum. If you ONLY want to rollback a section of a loop then set everything above it to NotSupported, and then set the section of the loop to Required. Make sure nothing underneath that section is set to Required, but is set to Supported if you need that in a transaction as well. If you do this though those items set to Supported will join the transaction that you set to Required. You may not want this behavior.

I'm lost as it is, so i wouldn't worry about leading me down the wrong path. Having stepped back and looked up the output window (which i though was the same as the execution results) i have found an interesting error, which i haven't seen before.Error: 0xC001402C at RoutecardSSIS, Connection manager "<connection name here>": 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.".Have you seen anything like this before?Thanks,Grant

I don't have a lot of information on that error, but I think it occurs because the runtime trys to create a new connection for the transaction to run on. I believe this is from having a child object set to Required when a parent is set to Required.

Ok, I'm going to try and dig around for a bit with those errors. it seems that it may have something to do with the remote transaction settings in the component settings MSDTC section. I'll play about with these until i get a new error or the problem sorted. I'll keep posting what i find so hopefully a solution can be highlighted.Cheers for the help,Grant

Anytime you see the magic words "The partner transaction manager has disabled its support for remote/network transactions", you can bet its that MSDTC is not configured correctly, or even enabled, on one, some, or all of the machines involved in the transaction.

Ok,I've been doing some digging around and i can now get the transaction working when calling the SSIS package from the development machine, which accesses a remote SQL server and also a remote share where the Excel files (Source) are stored. The problem was Windows firewall. Turning this off allowed the package to suceed. My big problem now is that it still will not work when deployed to a test server. Both the database and the package are then installed on the same server. It still will fail after executing the first iteration of the for each loop. There is no firewall running on the server, but i cannot see how this would make a difference as it is running all DB stuff on the same machine.Upon further investigation. It seems i can run the package from the SQL Server management studio when logged in as an administrator, yet when i am logged into the sever with the account that is used to execute the web service the package will not execute properly. I really need to find out what permissions are required to execute a transaction. Does anyone know??Does this possibly suggest anything else causing a problem. Any further help would be great.Thanks in advance,Grant

Correction to my previous post. It seems that the package can be executed sometimes under both accounts but can also fail. There is absolutely no consistency in how it works (or doesn't). It most definitely does not work when being called from the web service. Are there bugs in SSIS relating to what i am encountering.It would be nice just to have an error that can be seen consistently so that i can fathom out where it comes from.Grant

Grant, This sounds like a quagmire! I have no clue to what the problem could be, but this may be the time to start reducing items that could be the problem. WhatI would do is start with a new package, and add each piece of functionality one part at a time until you see exactly what is breaking. It's a pain in the arse, but at this point what else can you do?

Agreed. I've actually taken the approach to call a SQL agent job from my application, this seems to call the package with out any issues (transactions and all). I'll have to test further to double check this but its hopeful. I'll use treat the web service as a work in progress and work on it when i get time so that i can fully understand the problem.My reasons for useing the web service in the first place was so that the package would return its result once it had completed as apposed to once it had launched the job. The agent sends the outcome imediately.Cheers,Grant

Hello, I am facing problems while using transactions. I am using a foreach loop inside which i am doing following things:-- 1)load data from a file to a temporary table 2)if this load (1) is successful then truncate the data from the main table 3)Move the data from temporary table to the main table 4)archive the file i want to use transaction at step 2 and 3.In case step 3 fails then the truncation step shold roll back and data should be back to main table. I tried using transaction by setting transaction option for the package to requird and for the foreach loop container to supported but i am getting this error "[Connection manager "NHQGLBDM001.DHL"] 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.". " I have enabled DTC(distributed Transaction coordinator ) service. Any help on this... Regards Ruby

Hi All, When ever you get an error as below "[OLE DB Destination [532]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "DB_OLEDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed." [SSIS.Pipeline] Error: component "OLE DB Destination" (532) failed the pre-execute phase and returned error code 0xC020801C. if see further below in error list you will also see error as "[Connection manager "DB_OLEDB"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D024 "The transaction manager has disabled its support for remote/network transactions.". All above error are caused due to disabled MSDTC which need to be enabled for remote transactions. Please enable remote client inbound transactions Enable in MSDTC all ODBC Connections connecting servers ---> Admin Tools ----> Component Services ------> My Computer -->> Loacal DTC ---> Properties ---> Security ---> Tick Network DTC Access ---> Tick Allow Remote Client ---> Allow Remote Administration ----> Transaction manager Communication -----> Tick Allow Inbound and Tick Allow Outbount -----> select No Authication Required -----> Tick Enable XA Transactions Then click apply and ok will restart MSDTC Service. Thats it you need to apply above all servers you are connection through ODBC. Note: You also need to above steps where yours BIDS Instalation machine. Cheers, Raj Marikanti rajenmari