Take the query it's using to read the data (double click the little sql box) and put it into query analyzer to see the execution plan. There must be...

Who is not proofreading these questions and answers? The answer is obviously incorrect.

on the source, right click on the database and select copy. then paste it back to the same server. enter the new name, then back it up and...

Can you use '(local)' in the db connection so it always uses the local server?

View the job history of the distribution job. You should be able to go down thru the details to find out the exact comand that failed, which will have...

You have exchange server running on the same server as sql?  What is this for? some playground?

LOL.  How did you set up the profile?  control panel, mail, show profiles, click on the profile and do properites.  email accounts, view or change, next.  What does it say?

What is the configuration of the mail profile?  Is it hooked up to an Exchange server?

And the OS Account for SQLAgent owns the SQLMail profile?

Oops, missed a step. Expand management after expanding the server. 

What happens when you click the test button there too?

Expand enterprise manager for the server in question.  Right click on SQL Server Agent node, select Proerties.  What is the profile selected for mail session?

when you set up the operator and send a test message, what happens?

What is it that you are trying to debug in the package?  You can debug the vba bits by inserting a stop statement where you want to start debugging.  Make...

record a macro. select the pivot table. stop recording. step into the macro. change the pivotselect to enabledatavalueediting

should be something like:

Sub Macro1()

    ActiveSheet.PivotTables("PivotTable1").EnableDataValueEditing

End Sub

step thru the macro.  for code...

You might be interested in http://www.microsoft.com/downloads/details.aspx?FamilyId=A2EEF773-6DF7-4688-8211-E02CF13CBDB4&displaylang=en,  the Parallel Processing utility.