December 16, 2008 at 3:23 am
Due to the irritating limitation of SSIS to have packages greater than 3/4 MB in size before it starts to trip up, I've tried to split the package in to smaller child packages and run these from a parent package. My technique here may be a little wrong however so would appreciate some guidance. I've encapsulated the parent and child packages in the same project (is this correct?). I'm building the package in BIDS on my local pc for deployment on to a server and running in SQL Agent.
My first issue was that it looks like I have to have deployed the child packages first before I can design the parent as, when I put an execute package task in the parent package, it asks for a connection to a package in connection manager. I deployed the project first in it's transient state, and then went back in to the bids parent package on my pc and set the connection manager. As I wanted the parent package to run from the server, I had to change the path though from the drive mapping I:\... path to the full path \\servername\folder version.
The issue I now get though is when I complete the design of the parent with everything in the same project, I get the popup saying "The document is already open by another project". I get this as well if I run the parent package in debug mode, although it does run once you close down the dialog box.
I now see two versions of the child package as well in the project, which I assume is because of the server side connection manager version of the child package as well as my design version.
Where am I going wrong here please? Trawled the internet but haven't managed to find anything that helps me out. I think my problem here is too fundamental to warrant any forum entries!
December 16, 2008 at 10:52 am
Anyone got any pointers here for me please?
December 22, 2008 at 10:05 am
I think you may have an issue with your environment wherever you are creating the packages. There is no particular size limitation on packages. I regularly have developed packages around 5MB and even beyond 10 MB and do know some developers who have had packages 100MB in size !
You don't need to deploy the child packages before designing the parent. Just point to the child package file location where it is saved within your development environment.
Paul R Williams.
December 23, 2008 at 10:04 am
I've seen numerous blogs and sites sharing my issue with package size and microsoft have confirmed it (see http://support.microsoft.com/?kbid=952110). My original package got to around 16MB in size and I can no longer make amendments and save them on my pc (2GB RAM) without getting an out of memory exception error as a result. Perplexed as to how your colleague can have a package of size >100MB!
I'm trying to reference the path using the full path notation //servername/share/folder rather than the drive mapping version on my pc i.e. I:/folder etc. Having done this on my pc I get the server version as well as the a design version in my solution with the document open error popping up.
December 24, 2008 at 1:53 am
Yes it is true that there is an issue with size when using many Script components or Script transforms in the package which does affect the size of the package. I agree as I have had the same problem myself. Larger packages are quite possible though obviously without the many script components.
In relation to the other problem I'm not entirely sure what the actual issue is. Why can't use just reference the child package to the local machine where it is obviously being stored in your project. Use package configs for your deployment to reference the change in location. I've never had to deploy parent and child packages separately in order for them to work, but maybe I'm not understanding your problem correctly ?
Paul R Williams.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply