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