InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 26 May 2013 03:22:20 GMT20http://www.sqlservercentral.com/Forums/Topic1406572-2632-1.aspx[quote][b]Henry B. Stinson (1/14/2013)[/b][hr]Two problems I see right off:1. You may not want isolation level serializable for the whole package. That may cause unnecessary blocking in other processes.[/quote]I suppose you are rigth , I should be enough to set isolation level serializable for the single components[quote]2. if the data flow fails, the semaphore is not released.[/quote]If the data flow fails, the semaphore is released . The semaphore is designed in such a way, that cannot remain unreleased if the session terminates The semaphore is acquired by putting a lock on a row : begin tran select * from drop_fi_semaforo with ( rowlock, UPDLOCK, holdlock) where Uno = '1'There is nothing to commit or rollback , since data are not modified . If the session is terminated, the lock is released, the DBMS takes care of it Tue, 15 Jan 2013 02:09:35 GMTFederico Iorihttp://www.sqlservercentral.com/Forums/Topic1406572-2632-1.aspx[quote][b]Cade Roux (1/13/2013)[/b][hr]Any particular reason that sp_getapplock wasn't used for this implementation?http://msdn.microsoft.com/en-us/library/ms189823.aspx[/quote]Thanks for pointing this out... My predecessor used a data update of all rows with a fake column value to get around this and the whole thing was a nasty can of worms. sp_getapplock is so clean!Mon, 14 Jan 2013 12:00:24 GMTSAinCAhttp://www.sqlservercentral.com/Forums/Topic1406572-2632-1.aspxTwo problems I see right off:1. You may not want isolation level serializable for the whole package. That may cause unnecessary blocking in other processes.2. if the data flow fails, the semaphore is not released.Mon, 14 Jan 2013 08:56:56 GMTHenry B. Stinsonhttp://www.sqlservercentral.com/Forums/Topic1406572-2632-1.aspxRelease the semaphore : just end the transaction that is holding the semaphore by a commit ( or rollback, is the same since no data are modified) ...Mon, 14 Jan 2013 06:02:14 GMTFederico Iorihttp://www.sqlservercentral.com/Forums/Topic1406572-2632-1.aspxThanks for the post. A useful addition would be how to release the semaphore from within the package. A fairly common use case is to have a queue and want to hold the semaphore while you are selecting the next item from the queue. Ill also check out spappLockThanks again.Mon, 14 Jan 2013 05:58:55 GMTgreg 15422http://www.sqlservercentral.com/Forums/Topic1406572-2632-1.aspxYes, you are right, built-in sp_getapplock could be used in my solution instead of the user-defined semaphore table drop_fi_semaphore, but the structure of the SSIS package would remain unchanged .The only changed block will be Get Semaphore .Mon, 14 Jan 2013 02:50:32 GMTFederico Iorihttp://www.sqlservercentral.com/Forums/Topic1406572-2632-1.aspxAny particular reason that sp_getapplock wasn't used for this implementation?http://msdn.microsoft.com/en-us/library/ms189823.aspxSun, 13 Jan 2013 22:54:31 GMTCade Rouxhttp://www.sqlservercentral.com/Forums/Topic1406572-2632-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/T-SQL/93759/">Using a TSQL semaphore in SSIS</A>[/B]Sun, 13 Jan 2013 22:50:05 GMTFederico Iori