SSIS package reacts slow in BIDS

Question

SSIS package reacts slow in BIDS

Franky Leeuwerck

SSCrazy

Points: 2036
More actions
March 21, 2008 at 8:58 am

#120532

Hello,
One of my packages in my SSIS solution is reaction very slow in the BIDS UI.
Selecting a task in the Data Flow or the Even Handler tab takes minutes to accomplish.
The other (smaller) packages in the solution react very well, but this one is quite challenging to work with.
Any one an idea ?
Regards,
Franky
Franky L.

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

How much memory do you have? Is the package solution stored in a network location or on the PC itself? At my location our My Documents location is on a file server and the response loading and navigating an SSIS project can be slow. Upgrading my RAM helped, but still some issues occasionally. The project is stored as an XML file so navigating it will be slower the larger it gets.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Franky Leeuwerck SSCrazy Points: 2036 More actions · Answer 2

Hi Jack,

Thx for the update.

"How much memory do you have?" - 3,37 GB

"Is the package solution stored in a network location or on the PC itself? " - stored on the server itself

"The project is stored as an XML file so navigating it will be slower the larger it gets. " - I have no idea if there is a limit on the nr. of dataflow tasks. My package has about 30 tasks, mostly lookup's and there is 1 multicast task (main path splits into 5, but then is immediately rejoined into the main path again after 1 step).

As a test, I have copied the particular package to another solution but that rendered the same result : it takes about a half minute to swith between the control flow tab and the data flow tab in this package.

Regards,

Franky

Regards,

Franky

Franky L.

schumi-440251 Mr or Mrs. 500 Points: 515 More actions · Answer 3

A colleague had the same problem. Unfortunately i don't remember the exact property that solved it, but one of the tasks was connected to large data, I believe we change the property "DelayValidation" to true or some property that means the same thing. What is happening is that it is validating/reading large amount of data while you are in design mode where you don't need that. It worked for us, so hope this helps.

Franky Leeuwerck SSCrazy Points: 2036 More actions · Answer 4

Hi JT,

Thx for the update.

I put all

DelayValidation properties on True, and

ValidateExternalMetadata properties on False.

It still takes 30 seconds (not much a difference) to switch between the control flow tab and the data flow tab (or the event handlers tab).

Regards,

Franky

Franky L.

schumi-440251 Mr or Mrs. 500 Points: 515 More actions · Answer 5

have you tried "delayValidation = true" on the Connection manager? just go to the properties of the connection itself and change that.

Franky Leeuwerck SSCrazy Points: 2036 More actions · Answer 6

Hi again JT,

Yes, I had put DelayValidation=True for the ConnectionManagers, but that didn't improve the response of BIDS for that package.

Thanks,

Franky

Franky L.

schumi-440251 Mr or Mrs. 500 Points: 515 More actions · Answer 7

just asked the colleague, he remembered changing the default value of BypassPrepare property under "General" in "Execute SQL task". And in data flow, changing the "Data access mode" of a DB Source to "SQL command" and put whatever you need like "select * from table"

thats all i can tell you about our experience, it could be that we had a different problem. But after doing the above changes we didn't have to wait for a long time every time we switch to a task.

hope it works for you too

Franky Leeuwerck SSCrazy Points: 2036 More actions · Answer 8

Hi again JT,

Thanks for your further sharings.

Unfortunately, these changes do not improve the responsiveness of the BIDS UI in this particular package.

Regards,

Franky

Franky L.

mrsnake SSC Enthusiast Points: 155 More actions · Answer 9

This is just another idea, I don't know if it is of any value for you.

We have one package that includes 30-40 lookups. We had to split the flow into 5, where each starts with a flat file source pointing at the same connection manager but not all columns are part of every flow. At the end the flows are merged in a Union All. This way response time improved a lot. But, above all, the package is much easier to maintain.

Ola

Franky Leeuwerck SSCrazy Points: 2036 More actions · Answer 10

My colleague found the solution.

It was actually the BIDSHelper tool that caused the BIDS SSIS solution to respond so slow on a large SSIS package.

After uninstalling BIDSHelper, the SSIS solution was reacting as fast as before.

Regards,

Franky

Franky L.

bluepaperbag SSCrazy Points: 2055 More actions · Answer 11

bluepaperbag

SSCrazy

Points: 2055

March 8, 2010 at 2:49 pm

#1130304

Just wonder how he found this solution?

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 12

Franky Leeuwerck (4/9/2008)
My colleague found the solution.
It was actually the BIDSHelper tool that caused the BIDS SSIS solution to respond so slow on a large SSIS package.
After uninstalling BIDSHelper, the SSIS solution was reacting as fast as before.
Regards,
Franky

Where is this BIDSHelper Program?

I'm running Windows 7 & Windoes Server 2008 R2 (both 64 bit) and I can't find it?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jack Corbett SSC Guru Points: 184394 More actions · Answer 13

Welsh Corgi (4/26/2012)
Franky Leeuwerck (4/9/2008)
My colleague found the solution.
It was actually the BIDSHelper tool that caused the BIDS SSIS solution to respond so slow on a large SSIS package.
After uninstalling BIDSHelper, the SSIS solution was reacting as fast as before.
Regards,
Franky
Where is this BIDSHelper Program?
I'm running Windows 7 & Windoes Server 2008 R2 (both 64 bit) and I can't find it?

BIDSHelper is an add-in you can download from CodePlex, http://bidshelper.codeplex.com/

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 14

Thanks.

I was actually trying to verify that I did not have it.

I have a package that was 5.6 MB.

All of a sudden it starting getting slow.

So I started deleting task and it is at 3.6 and a lot smaller before the problem started.

It takes for ever when you click on a connection to set the delayvalidation to True.

I can't find the Validate External MetaData Property.

I don't know what else to look at.

Thanks again.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/