Looking through the Progress tab, I see the Pre-Execute step taking a long time. There are only 2 data flows and the first executes promptly. The second is quite complex. When it has a lot of data to process, the Pre-Execute step takes quite a while, yet the actual execution (when I start to see components turning yellow and green) seems to take a reasonabe time. Has anyone seen this behaviour? Is there a way to mitigate it? On a related note, I'm wondering what to do inside some of my Script components. I have a StringBuilder which I create in my ProcessInputRow method. I'm wondering if it would be better to create it in the PreExecute step and just initialize it inside the ProcessInputRow methods. Any thoughts?Tom SQL Server MVP Toronto, ON Canada

Regarding the pre-execute step: how does your dataflow look like? Lots of lookup components? What do you have as source?MCTS, MCITP - Please mark posts as answered where appropriate.

I have only a couple of lookups and they are not that big. The source of the data is MS-Access.Tom SQL Server MVP Toronto, ON Canada

Any Merge Join in place?Please vote as helpful or mark as answer, if it helps Cheers, Raunak | t: @raunakjhawar | My Blog

Yeah, quite a few.Tom SQL Server MVP Toronto, ON Canada

I have a number of necessary sorts, but the biggest is only on 6,221 rows.Tom SQL Server MVP Toronto, ON Canada

Can we get rid of them by sorting the data by using the ORDER BY clause?Please vote as helpful or mark as answer, if it helps Cheers, Raunak | t: @raunakjhawar | My Blog

That's already being done. The existing sorts are necessary.Tom SQL Server MVP Toronto, ON Canada

Tom I had a similar case as mentioned here. This is what i did: Create physical tables(which would be trncated at end) and dump the data just before where you have placed the SORTCreate a new Data flow task and now use any suitable "SQL Source task" and use ORDER BY in the SQL statement targetting the new table.This how I got rid of all SORT's The package which refused to run earlier was now getting completed in an hour, which was really good as per the scenario I had in hand. Can this be done in your case?Please vote as helpful or mark as answer, if it helps Cheers, Raunak | t: @raunakjhawar | My Blog

Not easily. I just don't understand why it is impeding the Pre-Execute step. As I said in the OP, the execute step seems to run fine. Overall, the package takes 20 min on this particular data set. It's just that testing this is consuming a lot of time.Tom SQL Server MVP Toronto, ON Canada

The Sort transformation should have a performance impact during execution, not during the pre-execute phase. Do you use stored procedures or queries to fetch the data? If you run the package using DTEXEC instead of the designer, do you encounter the same issue?MCTS, MCITP - Please mark posts as answered where appropriate.

The only real time-hog in the PreExecute phase are the Lookup components. You might be able to verify that by switching some of them to Uncached. Your package may start executing earlier, but will probably take longer to execute. Try optimizing the Lookups by using SQL statements in them instead of selecting a whole table. As for your scripts, in general anything you can move into the PreExecute method you should. Creating StringBuilders or any other class, or reading SSIS variables should be in the PreExecute method. Remove as much code as possible from the ProcessInputRow method - because that's the one that gets called repeatedly. Talk to me now on

The lookups are populated by a CSV file and an Excel worksheet. Not sure if SQL is appropriate here. Also, they aren't that large. I'll pick through my script transforms and see what I can refactor.Tom SQL Server MVP Toronto, ON Canada

The lookups are populated by a CSV file and an Excel worksheet. Not sure if SQL is appropriate here. Also, they aren't that large. I'll pick through my script transforms and see what I can refactor.Tom SQL Server MVP Toronto, ON Canada