Locking of tables in required container
Hi, Ok so i have a ssis package that works in batches it takes records from a few tables(one at a time) populates similiar tables and then deletes from the current table inside the for each loop(set to required) i need it to roll back the last itteration if any step fails inside the loop. So the structure is loop < data flow -> exec sql(delete) -data flow2 -> exec sql2(delete) etc etc -> add iteration > my problem comes in while for some reason it appears all the tables within that loop are locked i dont have any truncates and i have set all the delaymetadatavalidations on the dataflows. My problem is this will be a nightly process and those tables need to be free from locking for other processes is there anyway to stop the locking on the tables ? Regards Curt
June 14th, 2011 6:32am

Hi Curt, Please check the similar thread.. you can use use EST and release the lock from your required table before populating your data.. Let us TRY this | Mail me My Blog :: http://quest4gen.blogspot.com/
Free Windows Admin Tool Kit Click here and download it now
June 14th, 2011 8:11am

slightly different as the next loop it is just going to lock the table again and interupt any concurrent processes
June 14th, 2011 11:35am

Hi Curt, For the last loop, a condition on the counter (the loop varaible) can be used as condition in the constraint of SSIS and whatever mentioned by ETLvs ETL can be tried. For rollback option, check the transactions in SSIS,http://msdn.microsoft.com/en-us/library/ms137690.aspx. I hope Supported transactoin should help you out here.http://deepaksqlmsbusinessintelligence.blogspot.com/ Happy to help! Thanks. Regards and good Wishes, Deepak.
Free Windows Admin Tool Kit Click here and download it now
June 14th, 2011 12:00pm

Hi Guys, THsi actually turned out to be somethign really simple in the data flow tasks on destination theres a check box for lock table i just needed to make sure that wasnt checked and now it appears that no locking is taking place Thanks for the help Regards Curt
June 15th, 2011 11:39am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics