CODEWITHSUNDEEP
sql-servervisual-studio-2015ssissql-server-2016ssis-2016
Maff
Maff

Reputation: 64

Disable parallel execution of SSIS packages at server level

Can anyone advise if there is a way to disable or limit parallel execution of SSIS packages at the server level?

I have an SSIS project with a large number of packages drawing from multiple data sources. My development and production environments are of a high enough hardware spec that executing all packages at once and letting them run to completion works fine. However, my test server has a much lower spec, and running all packages causes the CPU to max out and some packages to terminate unexpectedly.

I'm developing a Data Warehouse and currently building the staging tier. I have 50+ tables (and growing), and 50+ dtsx packages with each package populating a single table. Data sources include MSSQL, MySQL, text files and MDBs across multiple local servers.

The packages are deployed from a VS2015 project to SQL server, and all packages are queued for execution simultaneously via a script. After queuing the packages, SQL server pegs the CPU at 100% continuously causing connection timeouts to data sources, and sometimes unexpected package terminations.

Adding more hardware to the test server isn't an option, nor is combining tasks in fewer packages to leverage package-level maximum concurrency.

I want to restrict the number of packages SQL server is trying to run in parallel, at the server level.

Upvotes: 2

Views: 1892

Answers (2)

Maff
Maff

Reputation: 64

Changing SQL Server's max degree of parallelism setting from 0 to 1 seems to have resolved the issue.

Upvotes: -1

skk
skk

Reputation: 55

I had a similar problem and I'm solving it in a package launcher script. I run it in a loop until a certain number of n and then check how many packages from the project have the unfinished status. Then the script waits until a slot is free and starts the next packages.

Of course, it would be nice if MS introduced a project-level variable: Max Concurent Packages. Or at package level: Max Connurent Executions. It is probably difficult on their side and the multitude of scenarios makes them leave the problem to the programmer.

I also recommend you to replace these 50 packs with generic one. You can do the whole datawarehouse on 3 SSIS packages, which have, for example, 3 tasks each.

Upvotes: 1

Related Questions