A/Synchronous execution of SSIS ETL packages

Hi

I'd like to know if there's a way to control the execution of ETL packages, such that:
Different packages, or at least packages that don't access the same table or database run asynchronously with respect to each other; e.g., two different packages run at the same time
and
If a package is called for execution more than once by different requests, force them to run synchronously, or one after the other.If this is possible, what resources would it require? Is this possible under, say, a dual or quad processor machine?

Thanks.

Well, there is nothing to stop you running 2 packages concurrently (i.e. at the same time). They run as seperate processes so cannot interfere with each other in the process space. Of course, if they are competing for the same external connections then you may run into problems.

Not so sure about ensuring that executions of a package are queued up tho as you require. You may have to implement a process on top of your packages to check to see if a package is already executing before attempting to execute it.

I don't think the spec of the machine is the issue here but it may help to know what resources a package utilises: http://blogs.conchango.com/jamiethomson/archive/2005/05/29/1486.aspx

-Jamie

|||

I'm not sure about the big picture, and how these package are run, but here are two ideas to consider:

1) using master package with execute package tasks and appropriate precedence constraints

2) creating Agent Job for each package - the Agent runs maximum one instance of a job at a time, thus if you always go via Agent, you'll have at most one instance of same package running.

|||

You would need to maintain some kind of shared state in order for packages in seperate processes to 'know' if a singleton object was being consumed by eachother and prevent contention. You can typically use a semaphore to stagger execution of any Windows dependant processes; in the SSIS context you could use a semaphore from within a script task. Have a look in the .Net framework for synchronization primitives support.

|||MSMQ task does this smashingly. :)