I would like to store an integer variable that gets incremented and decremented (a counting semaphore for limiting concurrent requests to an external API). This would be easy, except I need a way to read/write this variable from an SSIS package that is run in parallel SQL Agent jobs. Right now there can be 0 to 5 instances of the SQL Agent job, and therefore the SSIS package, running at once.
What are my options for reading and writing this variable? The code that will be using this variable is written as a custom SSIS task in .NET.
It is not particularly important that the value is exactly right, as long as it's generally close I'm within a tolerance range. Exact would be great, but not required.
I have access to the file system, registry, database, server, and the SSIS agent as a whole, but I'd like to check this variable very often by 15-30 threads, which has historically caused issues using a file system method (I'm probably doing it wrong), and is IMO too intensive to store in the database. Correct me if I'm wrong. Storing in the registry prevents the variable from being accessible across a server farm.
If there's anyone out there that can help, I will gladly be your indentured servant.
If it is used as counting semaphore, why not actually use Windows semaphore object? System.Threading.Semaphore is .NET version of it, and if you specify the semaphore name in constructor - the Win32 object will be shared between all the processes that use this name.