Are SSIS packages safe? i.e. can be called in parallel?
If you view them as a database connection, they are as thread safe as they can be. I think the more important issue is the table that will be locked when the package hits it.
An SSIS package can only do what it is told to do. If you convert a table, it will likely be locked while the operation is in effect. This will force the jobs to be more or less serial unless they time out in the first place. If you're dealing with data, you can probably get away with things that run in parallel.
SO answer to your question is "Yes, but". This is basic data access that will determine if things can run in parallel.
source to share
Yes, SSIS packages are thread safe. You can also manage 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 configured with dependency. You can do this in a package, or from a main package calling child packages.
-
MaxConcurrentThreads property can be overridden on command line for DTExec.exe
If you need to mitigate locking and contention issues, you can set transaction isolation properties or write database queries using nolock hints. Obviously, you will need to watch out for any potential concurrency or dirty reading issues.
You can use a profiler or perfmon on the database that the packages are running on to look for blocking signs. Without getting into a lengthy discussion of database tuning issues, which is a fairly large topic in itself, look for large numbers in lock and lock wait statistics as a sign of possible bottlenecks.
source to share