Are SSIS packages thread safe? ie. can be called in parallel?

Question

By default, are SSIS packages thread safe?

Can they be called in parallel? (in general)

Answer 1

If you consider them as a connection to the database, they are as thread safe as they can be. I think the more critical question is the table going to be locked when the package hits it.

The SSIS package can only do what it is told to do. If you are transforming a table it will probably be locked while the operation is in effect. That will force the jobs to be more or less serial if they don't timeout first. If you are coping the data you can probably get away with stuff running in parallel.

SO the answer to your question is "Yes, but". It is the underlying data access that will determine if things can run in parallel.

Answer 2

Yes, SSIS packages are thread-safe. You can also control concurrency in several ways:

• An individual package can be set to run a limited number of threads. The package property 'MaxConcurrentExecutables' controls this.

• Packages to run sequentially can be set up with a dependency. You can do this within a package or from a master package invoking child packages.

• The MaxConcurrentThreads property can be overridden on the command line for DTExec.exe

If you need to reduce locking and contention issues you can set transaction isolation properties or write database queries with nolock hints. Obviously, you would need to keep an eye on any potential concurrency or dirty read issues.

You can use the profiler or perfmon on the database running the packages to look for signs of locking issues. Without going into a lengthy discussion of database tuning issues, which is quite a large topic in itself, look for large figures in latch and lock wait statistics as a sign of possible bottlenecks.