So if you have not done much performance tuning, an interesting thing for you to do is look at the estimated or actual execution plans with the CASE statements and...

Without looking at the execution plan, it is hard to say, but I would guess that the query optimizer is unhappy with the case statements and you are getting table...

It is indicating a LEFT OUTER JOIN

The ODBC driver you are using looks like it is producing a bad data set.

See if there is a better driver to use.

Since it is reporting an XML parsing error,...

I would start with just turning on the default memory limits in the lookup component.

The fact that you are joining your two tables by fields wrapped in functions:

LTRIM(RTRIM(ACC.ACC_NO))= LTRIM(RTRIM(SAC.ACCOUNT))

forces a table scan when your query runs.

The engine must evaluate the function on every row...

I am going to ignore your cursor issue and just point out a couple of other items related to your script.

Rather than using sysobjects, the information_schema.tables view will help avoid...

There are a couple of reasons to put indexes on another disk array.

Most likely, they wanted another set of read and write heads for the index searches. This can...

Use the "DelayValidation" property on the package.

Typically, I would care about two things, the time it takes for the entire task to complete (pre-execute caching and actual execution) and the amount of system resources needed to...

There is not really a defined limit, but memory may be a problem with 20 million rows being cached in a lookup. You need to performance test it to...

Empty string resolves to zero when converted to FLOAT or INT. Try this:

SELECT CONVERT(FLOAT,'') AS EmptyFloat, CONVERT(INT,'') AS EmptyInt

This is by design - I cannot remember why.

I'm sure someone...

If you are spending the money and doing it correctly, your company will be purchasing VMWare licenses and using a version of VMWare that directly virtualizes rather than running over...

Yes, Reporting Services will output to a Word document, Rich Text, pdf, and a couple of other formats.

It can be tricky to make a really complicated Word document with it,...

There is a bug relating to this situation. Look for the hotfix on the MSDN website.