CODEWITHSUNDEEP
c#.netmultithreadingssiscross-process
Jordan
Jordan

Reputation: 32522

What options do I have for SSIS cross-process communication?

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.

Upvotes: 3

Views: 439

Answers (2)

Michael Entin
Michael Entin

Reputation: 7744

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.

Upvotes: 2

barrypicker
barrypicker

Reputation: 10088

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?

Upvotes: 0

Related Questions