April 18, 2008 at 9:00 am
I have a package with 96 connection managers, which takes absolutely ages to load up when I start up SQL Server Business Intelligence Studio. I have to alt tab to do something else while I wait for it to load! Similarly when I set it to run in debug mode. Now I believe this is because it's validating every connection. Is there anyway I can speed up the initial load when opening BIDS, and also when I run the package in debug mode please?
One of the connection managers was linking to a flat file successfully until the data source's format got changed by one of our software suppliers. This basically crashed the package. I took out the connection manager and data flow tasks that point at that file but my package is still very flaky. The error which occurs at different times in the package states:
Cannot detach from one or more processes The object invoked has disconnected from its clients.
Do you want to terminate them instead?
This does seem to happen at random stages but it's causing my package to crash every time. Doesn't seem to be a valid error as it can happen while in the middle of a data flow task, the task appears red, and the error pops up when I click on the "package execution completed. Click here to switch to design mode or select stop debugging from the debug mode" link.
Anyone come across this before? I hope so as it's driving me up the wall!
April 18, 2008 at 9:31 am
That's a lot of connection managers. I assume you have lots of other objects in the package as well. How much memory is visual studio using to just open the package?
You could probably get the package to open faster by setting a few delayvalidation properties to True, but it will still have to validate the connections and objects when the package gets to them so it will not help overall performance.
My first suggestion would be to cut the package into a few pieces and reduce the complexity.
As far as the deleted connection and components still causing you errors, I would guess that the removed components are not causing the trouble and it is just a matter of the package having gotten to the point at which it is using so much memory that is cannot manage it all. It could simply be a conicidence that this happened around the time you had the other issue.
Open the package and make sure VS does not take more than about 1/4 of the physical memory on your PC. If it does, you run the risk that VS, the debug host, and any other applications running at the same time will run you too low on memory. This has caused a lot of flakey issues on me in the past and the only thing I found I could do is break up the package.
April 23, 2008 at 3:02 am
Thanks for your post here. I persevered in the end with the speed issue as I would have to copy variables and all sorts across packages etc, but what seems to be causing problems is that the software supplier providing us with the flat files keeps on changing the structure of these flat files without informing me. This tends to cause havoc with SSIS it seems. It's bad practice by them really as they've published user documentation along the lines I'd already programmed. Shows bad planning too I think.
Anyway, they do provide sql scripts showing create tables for the current structure they are using. Only problem is I've got no idea of how to program something in ssis that would allow the structure of the ole db destination to vary in structure unless I put a sql script task in between the connection from the flat file to the ole db destination. Not sure if that will work, but then again, I think I'll be in more trouble with the flat file source as I don't think I have that flexibility with the structure of flat file sources. This is where the problem is occurring above.
Any ideas?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply