All, I have a large package (I know the recommendation is to have one package per data flow.) It has 20+ data flows in it. Personally I have found it much too complex to manage dozens of packages just because I want to have more than one data flow in my package. I have been working on this package in an iterative manner over the past several months. Recently, I noticed I started getting the error message: "Exception of type 'System.OutOfMemoryException' was thrown" when I went to save my package. This is _extremely_ frustrating, because when this happens, all the changes I have made are generally lost. Occasionally, I have noticed if I close some other BIDS windows, or just wait a bit, I will be able to click save again, and it will actually save. Usually, I am forced to just "end-task" Visual Studio. Other than splitting the package up into 20 separate packages, is there a way around this problem? I would rather put up with the lost changes than switch to dealing with 20 separate packages. It just makes things to difficult to manage when everything is split up into so many packages - particularly when I am passing variables around from parent to child packages. Please help! Thanks, David Baldauff

My initial reaction was to try to convince you that having one data flow per package and a simple directory/project structure to manage your packages was really much simpler than having one giant package and all of these headaches, but I know that's not what you're looking for, so I won't go there. Some basic questions for you: 1) How big is the DTSX file for this package? 2) How much memory is devenv.exe using when you get these errors? 3) Are you actually running out of memory? You haven't actually posted any information about your problem scenario other than the fact that you're getting the error. The more details you can share, the more likely someone will be able to help.

would 5 packages with 4 dataflows each sounds like a good comprimise but my guess is there is a way to sub-divide logically. copy the dtx file a number of times and delete the bits you don't want. I do have one package with 30 data flows, however they are all small and simple, no varables etc.

1.) 10mb 2.) 500mb 3.) The system is not running out of memory.I have 3.2gb physical and only using 2.4gb ancedotally now (and just saw the error.) I am able to contiue working, but won't be able to save unless I somehow trick BIDS to free up some memory. The scenario is as I have mentioned - and it is pretty simple: 1. I open the package. 2. Make mods to the package. 3. When I click save it gives me the aforementioned error. More information on the package: -84 connection managers -20 data flows -a handful of other control flow items The reason for the large number of connection managers is that at any step in a data flow where the data is bad or there is an error I redirect the bad data to a file. I do not reuse these files for multiple reasons, and so the number gets sort of large. David Baldauff

Matt, >You haven't actually posted any information about your problem scenario other than the fact that you're getting the error. The more details you can share, the more likely someone will be able to help. The use case is pretty simple. I edit my package and I save. That's it. That is the problem scenario.

David C Baldauff wrote: Matt, >You haven't actually posted any information about your problem scenario other than the fact that you're getting the error. The more details you can share, the more likely someone will be able to help. I posted the use case very clearly. I edit my package and I save. That's it. That is the problem scenario. Fair enough. Please understand that you're dealing with a community of volunteers who spend their free time answering questions and helping other people solve their problems. Remote troubleshooting is an inherently difficult process, and the fewer details that are available, the more difficult it becomes.

Matt, I know. I appreciate all help I get on this forum. Trust me. (In fact I apologize ifI sounded upset -that was not my intention - I have edited it to reflect what I meant) I was just attempting to point out that the problem is very simple. I open the package. All it takes is for me to disable a control flow item (if anything this should reduce memory consumption) and save. Thanks,

Dear Microsofties, If you have time to take a look at this issue please do. It seems I have hit some kind of limitation in BIDS with regard to developing SSIS packages with larger numbers of connection managers. I believe as I look back on when this started happening that the large number of connection managers are causing this problem. Just reducing the number of connection managers seems like a silly solution, given that my BIDS process is only currently consuming 500mb. Please help. Thanks!

David, I'm experiencing the exact same problem. I have a number of data flow tasks in one somewhat large package. I am not running out of memory, but BIDS keeps telling me that I have and will not let me execute or save the package. Have you made any progress?

I think the best suggestion is to break your package up into multiple (two or more) parts. I think in my case the number of flat file connection managers out me over the top. I would seek a fix from Microsoft, but I think it would likely take less time to just spllit the package into two. Which, in theory, will probably make the issue go away for me. If this is not a possibility in your case, then Microsoft is probably your next best path. HTH

All, In the end I did split my enormous package into two packages. One thing that surprised me was that my overall execution time went down by almost half. So, apparently the memory consumption became a runtime problem as well as a designer problem (mentioned above.) So, I think there is definitely good reason to at least try to stick with the Microsoft line on this - one transformation, one package. In practice, for some things, I think this is overly complex, and requires significantly increased work. That being said, depending on your use case, it may make all the sense in the world. I think at the end of the day, as a good friend of mine likes to say, "it depends". Regarding splitting the package into two: the easiest way to do this for anyone wanting to do the same is simply to make a copy of your package and then delete half of the items in each of your copies of the package. Otherwise, as you probably have experienced, copying and pasting the control flow items from one package to another is messy at best.

Hi, It is a bug in SSIS. A fix has been provided by MS for this problem. Please find the details below: -----Original Message----- From: v-2rones@mssupport.microsoft.com [mailto:v-2rones@mssupport.microsoft.com] Sent: Monday, February 22, 2010 4:40 PM To: Mor, Ricardo Subject: Hot fix ready for your incident 100222-007908-021 CASE_ID_NUM: 100222-007908-021 MESSAGE: Hello, The hot fix for your issue has been packaged and placed on an HTTP site for you to download. WARNING: This fix is not publicly available through the Microsoft website as it has not gone through full Microsoft regression testing. If you would like confirmation that this fix is designed to address your specific problem, or if you would like to confirm whether there are any special compatibility or installation issues associated with this fix, you are encouraged to speak to a Support Professional in Product Support Services. The package is password protected so be sure to enter the appropriate password for each package. To ensure the right password is provided cut and paste the password from this mail. NOTE: Passwords expire every 7 days so download the package within that period to insure you can extract the files. If you receive two passwords it means you are receiving the fix during a password change cycle. Use the second password if you download after the indicated password change date. Package: ----------------------------------------------------------- KB Article Number(s): 944840 Language: English Platform: i386 Location: (http://hotfixv4.microsoft.com/Visual%20Studio%202005/sp1/vusqfe5250/50727.1809/free/338927_ENU_i386_zip.exe) Password: z-7*WnM6 NOTE: Be sure to include all text between '(' and ')' when navigating to this hot fix location!

This is _extremely_ frustrating, because when this happens, all the changes I have made are generally lost. Occasionally, I have noticed if I close some other BIDS windows, or just wait a bit, I will be able to click save again, and it will actually save. Usually, I am forced to just "end-task" Visual Studio. You should be able to recover your changes using the following method: Right-click your package in the Solution Explorer and select View Code. Then copy the XML to the clipboard and paste it into another editor. After closing/re-opening your BIDS, switch again to the package's code and paste the version in memory over the one on your screen. Changes recovered! Please note that I experience this with SQL Server 2008, so I'm not 100% if the behaviour is exactly the same as for being able to copy to the clipboard while VS is complaining about memory problems.