3

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.

Jordan
  • 31,971
  • 6
  • 56
  • 67
  • 4
    Just wondering, any reason this value can't live in a database table? All 5 instances should be able to read it, you'd have to use appropriate locking if applicable, or maybe I'm over simplifying your issue? When you need to fetch or update the value you can do it via code in the custom task via SqlClient. – Ta01 Mar 03 '11 at 16:33
  • The main reason is simply the amount of talking with the database I'm doing. My outline above is somewhat simplified, in that each of those 5 instances can have up to 20 threads all accessing the variable in a loop, waiting for that variable to be under a certain threshold. Worst case scenario puts that at around 100 queries per second, assuming that I sleep the threads for a full second. Right now they're sleeping for 100ms waiting for a local process variable. Hope this helps. – Jordan Mar 03 '11 at 16:55

2 Answers2

2

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.

Michael Entin
  • 7,189
  • 3
  • 21
  • 26
  • You're the man. Thanks for the information--I wish I would've known about this class a few months ago. For some reason it never came up during searching. I will give this a shot when I'm back at work. – Jordan Mar 14 '11 at 01:10
0

Not sure I understand the question - you indicated you have access to a Database, file system, registry, etc. Are you saying you don't want to / can't use these methods? Are you looking to persist the value so in the event the computer halts you can recover?

If persistance is not required, you could persist in memory via an RPC, including COM or web services. Whatever the solution, it seems it needs to be global and visible to all running instances.

Is this variable metadata used as a semaphore to control and coordinate the processes, or is this variable domain data?

barrypicker
  • 9,740
  • 11
  • 65
  • 79
  • I'm looking for input on how I can use any of the above methods, or anything I don't know about that's available to control access to this variable. It is indeed a counting semaphore used to coordinate the number of concurrent requests to an external API. Persistence is not required, since I have a queuing system that will pick up where it left off if something fails. – Jordan Mar 03 '11 at 19:11
  • There are quite a few concepts to consider. Does the external process operate with multiple threads, or does multiple instances of the external process operate each with a single thread? If the external process contains multiple threads, (and assuming this external process is mutable) you can add a counter to count the number of threads, include an appropriate interface into the external process to retrieve the count. If the external process is not multi-threaded, but rather has a single threaded, with multiple instances the use of a process count would provide these details. – barrypicker Mar 16 '11 at 18:13