Question about using variables in script task
The last time I used SSIS was with SQL Server 2008, and pretty straightforward ETL tasks with some conditional branching, etc. I am getting into SQL Server 2017/Visual Studio 2017 and maintaining some existing packages.
I have come across some code in script tasks and having some difficulty finding online documentation. I am finding the properties LockForOneRead(), LockForOneWrite() on VariableContainer() and LockForRead() and LockForWrite() on Variables and an Unlock() property. I finally did find something that gives me a little insight, but still a little unsure and hopefully an expert can give me a little more insight.
It seems to me that unless you lock a variable in a very long running script task (most of the code I've seen locks, gets value, sets another string value, etc., or updates a table with a parameter value, etc., then unlocks), the likelihood of two processes accessing the same variable(s) is minor. Perhaps I haven't gotten into complex package coding yet, but most of what I've seen is pretty much synchronous flow where you wouldn't even have multiple processes running at the same time.
Thanks for the help.
I have come across some code in script tasks and having some difficulty finding online documentation. I am finding the properties LockForOneRead(), LockForOneWrite() on VariableContainer() and LockForRead() and LockForWrite() on Variables and an Unlock() property. I finally did find something that gives me a little insight, but still a little unsure and hopefully an expert can give me a little more insight.
- If the idea is to lock a variable so competing processes within the package aren't accessing it at the same time, what happens if process A locks a variable and process B tries to lock the same variable.
- What happens if I forget to unlock the Variables collection in my task? Will it be unlocked when the script task goes out of scope?
It seems to me that unless you lock a variable in a very long running script task (most of the code I've seen locks, gets value, sets another string value, etc., or updates a table with a parameter value, etc., then unlocks), the likelihood of two processes accessing the same variable(s) is minor. Perhaps I haven't gotten into complex package coding yet, but most of what I've seen is pretty much synchronous flow where you wouldn't even have multiple processes running at the same time.
Thanks for the help.
lcohan
Theoretically you can execute the same SSIS package concurrently as SQL should create an instance for each execution and there's an article at http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/70346/ that describes few ways you can achieve that on an older version of SQL howvere it should still work in my opinion on newer versions but of course this has to be testes first.
ASKER
I can see that possibly happening, although don't foresee it as a possibility in our current environment. My main reason for asking is to answer the two main questions in the above bullets, and I guess primarily, what happens if you forget to unlock the variable(s).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Seems like the best way to handle it would be to use implicit locking. The concept of locking variables just seemed strange to me.
Even with SQL Server, the required locking mechanism is handled behind the scenes, but you have control if needed (e.g. using lock hints), which are automatically released at the end of a transaction (assuming you are either using implicit transactions or you are in an explicit transaction that you release with a commit or rollback).
Seems like you could get in trouble sharing a variable between asynchronously running processes that could change in value, unless you had some very explicit way of controlling the flow. Does process A set the value and process B read it and can you insure that process A runs before process B tries to read it. Of course, if it is synchronous, it doesn't matter because process B would run after process A and locking would not even be required.
Sounds like a lot more complex solution than I'd ever be involved in working with.
However, just for clarification, it sounds like, if I try to read/write a variable without locking it, I'd get and error and if I forget to unlock a variable it could have dire consequences down-line if another task tried to read it, correct?
Even with SQL Server, the required locking mechanism is handled behind the scenes, but you have control if needed (e.g. using lock hints), which are automatically released at the end of a transaction (assuming you are either using implicit transactions or you are in an explicit transaction that you release with a commit or rollback).
Seems like you could get in trouble sharing a variable between asynchronously running processes that could change in value, unless you had some very explicit way of controlling the flow. Does process A set the value and process B read it and can you insure that process A runs before process B tries to read it. Of course, if it is synchronous, it doesn't matter because process B would run after process A and locking would not even be required.
Sounds like a lot more complex solution than I'd ever be involved in working with.
However, just for clarification, it sounds like, if I try to read/write a variable without locking it, I'd get and error and if I forget to unlock a variable it could have dire consequences down-line if another task tried to read it, correct?
I believe you got the right conclusion that leaving SQL to do the work is supposed to be doing may be the right and easiest thing even though by doing this we may encounter sometimes the unfortunate packages failures due to deadlock/lock timeout on variables.
Also please keep in mind that hints are not guaranteed as thats just what they are - "hint" plus there's flexibility at the SSIS package for instance level to run certain steps sharing same variable conditional/serialized VS. parallel if this does not cause excessive execution time or other business rules issues. also number of parallel running tasks, defaultBufferMaxRows, and defaultBufferSize are a few settings you could adjust to avoid severe issues while SSIS tasks are running in parallel.
Also please keep in mind that hints are not guaranteed as thats just what they are - "hint" plus there's flexibility at the SSIS package for instance level to run certain steps sharing same variable conditional/serialized VS. parallel if this does not cause excessive execution time or other business rules issues. also number of parallel running tasks, defaultBufferMaxRows, and defaultBufferSize are a few settings you could adjust to avoid severe issues while SSIS tasks are running in parallel.
ASKER
Thanks for the info.