what i want is to know what are the requirements to execute a dts with steps joined to transactions in it. I talk about requirements related to services, user privileges and so on
I want to know this info that applied for a general problem (that is how to execute a transactional dts).
Anyway i will tell you what is my specific problem related to it. I have a DTS who loops over files in a directory processing each file (that is inserting rows in several tables of a sql server database) in a way that all files must be processed or if one is not processed due to an error (logged to a log table) then all previous files must be deprocessed (changes made to database must be 'rollback'ed). Thus transacctions are needed into the DTS for some steps (those who change database) while others mustn't (those who perform tests and just write to log table)
This is so easy to do in a DTS but i have experienced several problems due to transacctions. first, with MSDTC service running in server machine (where SQLServer is running) I cannot execute successfully the dts unless MSDTC also running in my local machine. Also is not possible for my user to execute DTS from dts editor unless my user being sysadmin in SQLServer server machine. So i want to know what is exactly necessary to execute such kind of DTS (transactional DTS) in terms of services, security, user profiles and roles, etc