Scenario: I am using Excel Source then some lookups, and finally a OLEDB destination. IF I am reading 10 rows from excel then after performing lookup on those 10 rows for watever rows I am not finding any matching values , I am dumping them in HTML error file saying that for these many rows no matching values was found. And further validation cleared data is moving forward. I need to implement a menthod where if any of the rows fails in lookup and moves towards non matching rows then in that case I dont want my res of rows to proceed forward to OLEDB detsination. But I also want to make sure that in one go all my validations are checked corresponding to all lookups and if any rows are non matching then it should be logged in html file so that end user can edit them and then dump them back. For clarification: I want to transaction in whole either my package should insert all 10 rows or none, but as no where package is failing ,Transaction property will not work here for me, Variables are not helpful as we cannot read variables values inside same data flow task where we declared it. I need a mechanism where I can buffer my Excel rows in memory take the count of them and then match same count with source counbt if they match then insert them or else faile the package ! Let me know if their is any genius who can give solution to this or this just cant be implemented in SSIS.

Hi, See how to use explicit SQL transaction without inbuilt SSIS support for transaction in below post.. http://consultingblogs.emc.com/jamiethomson/archive/2005/08/20/SSIS-Nugget_3A00_-RetainSameConnection-property-of-the-OLE-DB-Connection-Manager.aspx But, I don't think its necessary here, you can fire failure event from script component in case of missing lookup values. Have a look at below post too see http://microsoft-ssis.blogspot.in/2011/02/script-task-and-component-logging.html - Chintak (My Blog)

Dear Chintak, I have implemented 50 Lookups and I need to execute all lookups at once so that I can put all errors in front of end user at one go...If anywhere in between I will raise an failure event means that my package will not execute completely and hence all errors will not be found in one go, which in other words means that I need to re-execute package again and again to get to all errors one by one in log files...... I need a mechanism where all 50 lookups are executed at once despite of matching or non matching records and in last I can compare that how many rows reached at end if that count is less than source count then no insert in OLEDB else it should insert !

Hi Gaurav_X, I suggest you can put all the tasks in a Sequence Container, and then add two Execute SQL Tasks after the Sequence Container, one is for Commit Transaction, the other is for Rollback Transaction. Please note that you need to change the RetainSameConnection property of the database connection from false to True. Details step please refer to:http://microsoft-ssis.blogspot.com/2011/09/ssis-transactions-with-tsql.html Thanks, Eileen

Thanks for response Eileen. Right now I have implemented like same, a explicit BEGIN Tran and on errors Explicit Rollback Tran in one sequence Conatiner,I was amazed to see that I couldnt implement Transactions inside Sequence Container. I mean when I am using transactions I am errors. Incomaptible Transaction Type with Isloation Level. I have posted another thread for same. Anyways Thanks Eileen you anwsre is satisfactory abd already implemented !