How big is too big for an SSIS package?

Question

How big can a SQL Server 2005 SSIS package definition become before design-time or run-time performance is impacted? I'm not talking about the size of the datasets being passed, or even the number of columns being returned. I'm just talking about the number of sequences, tasks, data flow tasks, and variables being used in the package. I have a package that I'm less than halfway through with implementing, and I've got a total of 20 sequences (some nested), 16 data flow tasks, and 28 non-data flow tasks (mostly Execute SQL and Script tasks). The .dtsx file itself is 4MB so far.

By the time I'm done, I could see the packge easily double or even triple its current size. I haven't seen any performance issues yet, but I want to know if I'm going to encounter any. Has anyone else run into design-time or run-time performance issues with large package definitions? Is there a best practice out there for package size limitations?

Answer 1

It does sound like it's time for you to break that package down into separate packages, and use the Execute Package Task to execute them. That's much too large.

Answer 2

Two issues arise from large packages. The first as @Andrew mentioned is in the maintenance of a large package regardless of performance issues. Especially if you are working in a team environment, if you have the one large package checked out, you will have to be careful of merging changes etc. The second problem, which I have seen in SQL 2005 has to do with the fact that the runtime for SSIS in BIDS is a 32bit process. With packages as large as you have mentioned, It is very likely that you will soon run into out of memory issues while trying to execute that package in your dev environment. Most people recommend breaking the packages up in to much smaller units to simplify team development and unit testing.

Here is a specific recommendation from a knowledge base article http://support.microsoft.com/kb/952110/en-us
that mentions 7mb as a .dtsx size that can start to cause problems.

As mentioned above, the root cause is that the runtime for SSIS/BIDS (DevEnv.exe) is a 32 Bit process and many subsystems in that exe create their own private heaps that don't release memory (xml, oledb, etc.) See this response to a similar question:
http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/7ead0bee-6f04-4778-83b7-0fd666833113/