MAXDOP for SSIS

Both @teshome and @Usman are right but.

As @teshome mentioned, you can set max degree of parallelism in each query SSIS is doing, but this will not prevent a SQL Server overloading if too much concurent executables are running on the SSIS side and multiple heavy queries executed against the database server

AS @Usman mentioned, the Engine Threads and MaxConcurentExecutables will allow you to control load of SSIS server and also load of database server in case multiple packages are being executed and sending multiple queries to the database server. Again if will have single not well optimized query it can nearly kill your database server.

So to properly balance the load you should combine both the techniques.

If you have Enterprise version of SQL Server then the best and easiest way how to handle the workload on the server woul be use of Resource Governor. Using it you do not need to touch any of the SSIS packages if you write appropriate classification funtion.

See Managing SQL Server Workloads with Resource Governor for details on Resource Governor.

But even you will use Resource Governor, it is important to write properly tuned queries and allocate the load of SSIS server too.

Parallelism is handled in SSIS by two properties

1. MaxConcurrentExecutables, a property of the package. It defines how many tasks can run in parallel. The default value is -1 which means the number of processors + 2.
2. EngineThreads, a property of the Data Flow Task that defines how many work threads the scheduler will create and run in parallel. Its default value is 5 in SQL Server 2005 and 10 in SQL Server 2008, with a minimum value of 2. However, the engine will not use more threads than it needs, regardless of the value of this property. The engine may also use more threads than specified in this property, if necessary to avoid concurrency issues.

Since, you do not have a dedicated server to SSIS, a low value for MaxConcurrentExecutables could help you out. But what would be the best value, that you have to sort out through testing and according to the workload.

you can use the option parallelism hint in your query or stored procedure. something like

select * from x option (MAXDOP 6)