 | |
Deadlock when accessing the LockOneForWrite/Read method is SSIS
microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Deadlock when accessing the LockOneForWrite/Read method is SSIS -
04-23-2006
, 06:46 PM
#2
| |||
| |||
|
|
I'm writing my first SSIS package and am running into some problems that I could use a hand with. i'm using a For Loop Container to do two things: 1) Use a FTP task to look for a file on a remote server 2) On Success use the Script Task to set a package variable to False so the loop stops and the packages continues. If the file is not presnet I set the thread to sleep for 5 minutes using the Script Taks and loop to check again. I created a package level variable called bolSTIUpdateInProcess (default val = True) The EvalExpression of the loop is set to @bolSTIUpdateInProcess = True The following script runs when I want to exit the loop Public Sub Main() Dim vars As Variables If Dts.Variables.Contains("bolSTIUpdateInProcess") Then 'The next lines produces a deadlock error Dts.VariableDispenser.LockOneForWrite("bolSTIUpdat eInProcess", vars) vars("bolSTIUpdateInProcess").Value = False vars.Unlock() End If Dts.TaskResult = Dts.Results.Success End Sub The packages produces an error when I try to execute the LockOneForWrite method so I can change the value of bolSTIUpdateInProcess. Is the variable locked because the EvalExpression of the containing For Loop is using it? If anyone could lend a hand I would appreciate it. |
#3
| |||
| |||
|
|
Your Eval expression is an assignment expression, did you just miss a = Are you not using the ReadOnlyVariables and ReadWriteVariables collections on the Script task designer? This will tell the task to do the locking for you Why would you need to check for the existence of the variable? Surely you created it and know it is there and also you are using it in your eval expression? OK. So if you include the variable in the Task's ReadWriteVariables Collection and in the task you try to lock the variable explicitly you will get a deadlock. If you remove the variable from one of these two places then you should be OK -- Allan Mitchell www.SQLDTS.com www.SQLIS.com www.Konesans.com "mystical potato" <mysticalpotato (AT) discussions (DOT) microsoft.com> wrote in message news:113C18BA-FE9A-4F34-96B3-37A16333BD45 (AT) microsoft (DOT) com... I'm writing my first SSIS package and am running into some problems that I could use a hand with. i'm using a For Loop Container to do two things: 1) Use a FTP task to look for a file on a remote server 2) On Success use the Script Task to set a package variable to False so the loop stops and the packages continues. If the file is not presnet I set the thread to sleep for 5 minutes using the Script Taks and loop to check again. I created a package level variable called bolSTIUpdateInProcess (default val = True) The EvalExpression of the loop is set to @bolSTIUpdateInProcess = True The following script runs when I want to exit the loop Public Sub Main() Dim vars As Variables If Dts.Variables.Contains("bolSTIUpdateInProcess") Then 'The next lines produces a deadlock error Dts.VariableDispenser.LockOneForWrite("bolSTIUpdat eInProcess", vars) vars("bolSTIUpdateInProcess").Value = False vars.Unlock() End If Dts.TaskResult = Dts.Results.Success End Sub The packages produces an error when I try to execute the LockOneForWrite method so I can change the value of bolSTIUpdateInProcess. Is the variable locked because the EvalExpression of the containing For Loop is using it? If anyone could lend a hand I would appreciate it. |
#4
| |||
| |||
|
|
Allan, thanks for the reply. I did miss an "=" in the expression (that's the VB in me). Maybe I should take a step back and just say what I'm looking to do and get some feedback based on that. I need to loop through a series of tasks until an event inside the loop occurs (e.g. I retrieve a file from an ftp server). How can I set some flag inside the loop which will indicate that the loop should end? thanks, Scott "Allan Mitchell" wrote: Your Eval expression is an assignment expression, did you just miss a = Are you not using the ReadOnlyVariables and ReadWriteVariables collections on the Script task designer? This will tell the task to do the locking for you Why would you need to check for the existence of the variable? Surely you created it and know it is there and also you are using it in your eval expression? OK. So if you include the variable in the Task's ReadWriteVariables Collection and in the task you try to lock the variable explicitly you will get a deadlock. If you remove the variable from one of these two places then you should be OK -- Allan Mitchell www.SQLDTS.com www.SQLIS.com www.Konesans.com "mystical potato" <mysticalpotato (AT) discussions (DOT) microsoft.com> wrote in message news:113C18BA-FE9A-4F34-96B3-37A16333BD45 (AT) microsoft (DOT) com... I'm writing my first SSIS package and am running into some problems that I could use a hand with. i'm using a For Loop Container to do two things: 1) Use a FTP task to look for a file on a remote server 2) On Success use the Script Task to set a package variable to False so the loop stops and the packages continues. If the file is not presnet I set the thread to sleep for 5 minutes using the Script Taks and loop to check again. I created a package level variable called bolSTIUpdateInProcess (default val = True) The EvalExpression of the loop is set to @bolSTIUpdateInProcess = True The following script runs when I want to exit the loop Public Sub Main() Dim vars As Variables If Dts.Variables.Contains("bolSTIUpdateInProcess") Then 'The next lines produces a deadlock error Dts.VariableDispenser.LockOneForWrite("bolSTIUpdat eInProcess", vars) vars("bolSTIUpdateInProcess").Value = False vars.Unlock() End If Dts.TaskResult = Dts.Results.Success End Sub The packages produces an error when I try to execute the LockOneForWrite method so I can change the value of bolSTIUpdateInProcess. Is the variable locked because the EvalExpression of the containing For Loop is using it? If anyone could lend a hand I would appreciate it. |
#5
| |||
| |||
|
|
You can do it how you are doing it or you can use the result of something to trigger the end so maybe your process asks the question "How may rows in this table?" In your loop you query the DB and assign the value returned from the COUNT(*) to a variable. So your init expression might look like this @myVar = 0 Your eval expression might look like @myVar <= @MyThresholdVar You are approaching it in the right way but I think in this example you are possibly trying to lock the same variable twice in the script task. Once in the designer and once in code. |
#6
| |||
| |||
|
|
I've changed the package to approach the eval expression the way you indicated ---LoopTask InitialExpression (@Threshold = 0) EvalExpression (@Threshold <= @Actual) ----Script Task ...Sub Dim vars As Variables Dts.VariableDispenser.LockOneForWrite("Actual", vars) vars("Actual").Value = 2 vars.Unlock() Dts.TaskResult = Dts.Results.Success ...End And again I'm using variables with a scope of Package, they are not read-only and I have them in the script task under the ReadWrite collection. And this did not solve the issue. I gave up and created a new project with just the For Loop, FTP and Script tasks and this still produces a deadlock. It is really ffrustrating that something this simple is such a problem. I hadn't mentioned this before but I'm working in VS05 Pro disconnected from sql. Would this make a difference in this instance? Sorry to keep bothering you with this. Would it help if I sent you the dtsx? Here's my address if you want to shoot me an e-mail (this way you don't have to post your address). sodonnel AT optonline.net "Allan Mitchell" wrote: You can do it how you are doing it or you can use the result of something to trigger the end so maybe your process asks the question "How may rows in this table?" In your loop you query the DB and assign the value returned from the COUNT(*) to a variable. So your init expression might look like this @myVar = 0 Your eval expression might look like @myVar <= @MyThresholdVar You are approaching it in the right way but I think in this example you are possibly trying to lock the same variable twice in the script task. Once in the designer and once in code. |
#7
| |||
| |||
|
|
No. Either lock the variables in the code OR lock them by placing them in the ReadWriteVariables collection not both. -- Allan Mitchell www.SQLDTS.com www.SQLIS.com www.Konesans.com "mystical potato" <mysticalpotato (AT) discussions (DOT) microsoft.com> wrote in message news:24FF8792-0DC0-4686-BEB8-B4D8D90A0D0A (AT) microsoft (DOT) com... I've changed the package to approach the eval expression the way you indicated ---LoopTask InitialExpression (@Threshold = 0) EvalExpression (@Threshold <= @Actual) ----Script Task ...Sub Dim vars As Variables Dts.VariableDispenser.LockOneForWrite("Actual", vars) vars("Actual").Value = 2 vars.Unlock() Dts.TaskResult = Dts.Results.Success ...End And again I'm using variables with a scope of Package, they are not read-only and I have them in the script task under the ReadWrite collection. And this did not solve the issue. I gave up and created a new project with just the For Loop, FTP and Script tasks and this still produces a deadlock. It is really ffrustrating that something this simple is such a problem. I hadn't mentioned this before but I'm working in VS05 Pro disconnected from sql. Would this make a difference in this instance? Sorry to keep bothering you with this. Would it help if I sent you the dtsx? Here's my address if you want to shoot me an e-mail (this way you don't have to post your address). sodonnel AT optonline.net "Allan Mitchell" wrote: You can do it how you are doing it or you can use the result of something to trigger the end so maybe your process asks the question "How may rows in this table?" In your loop you query the DB and assign the value returned from the COUNT(*) to a variable. So your init expression might look like this @myVar = 0 Your eval expression might look like @myVar <= @MyThresholdVar You are approaching it in the right way but I think in this example you are possibly trying to lock the same variable twice in the script task. Once in the designer and once in code. |
#8
| |||
| |||
|
|
Grrrr!! Thanks! It now works. Wrox's Pro SSIS doesn't really make that point (at least not early on in the book). I appreciate your patience and especially your help. Scott "Allan Mitchell" wrote: No. Either lock the variables in the code OR lock them by placing them in the ReadWriteVariables collection not both. -- Allan Mitchell www.SQLDTS.com www.SQLIS.com www.Konesans.com "mystical potato" <mysticalpotato (AT) discussions (DOT) microsoft.com> wrote in message news:24FF8792-0DC0-4686-BEB8-B4D8D90A0D0A (AT) microsoft (DOT) com... I've changed the package to approach the eval expression the way you indicated ---LoopTask InitialExpression (@Threshold = 0) EvalExpression (@Threshold <= @Actual) ----Script Task ...Sub Dim vars As Variables Dts.VariableDispenser.LockOneForWrite("Actual", vars) vars("Actual").Value = 2 vars.Unlock() Dts.TaskResult = Dts.Results.Success ...End And again I'm using variables with a scope of Package, they are not read-only and I have them in the script task under the ReadWrite collection. And this did not solve the issue. I gave up and created a new project with just the For Loop, FTP and Script tasks and this still produces a deadlock. It is really ffrustrating that something this simple is such a problem. I hadn't mentioned this before but I'm working in VS05 Pro disconnected from sql. Would this make a difference in this instance? Sorry to keep bothering you with this. Would it help if I sent you the dtsx? Here's my address if you want to shoot me an e-mail (this way you don't have to post your address). sodonnel AT optonline.net "Allan Mitchell" wrote: You can do it how you are doing it or you can use the result of something to trigger the end so maybe your process asks the question "How may rows in this table?" In your loop you query the DB and assign the value returned from the COUNT(*) to a variable. So your init expression might look like this @myVar = 0 Your eval expression might look like @myVar <= @MyThresholdVar You are approaching it in the right way but I think in this example you are possibly trying to lock the same variable twice in the script task. Once in the designer and once in code. |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.