SSIS package importing from RAW files to Excel does not save all data from JOB but works on local machine

I have an SSIS package that takes data from a SQL 2005 table, exports it to a RAW file, then the next step is it imports the RAW file data into an EXCEL .xlsx spreadsheet  using this as the destination connection:
    Microsoft.ACE.OLEDB.12.0
    EXCEL 12.0 XML;HDR=YES;

This package gets a list of values and does a for each loop for the values (16 different loops) to populate 16 different tabs in the .xlsx spreadsheet.  Each loop it takes the value and uses it to query a table to populate each tab on the spreadsheet, some tabs have very little data, others have hundreds of thousands of rows.

The problem I am having is that it works on my local machine (runs slow) but populates all the tabs.  When I move it to the server it runs and populates the first 3 or so tabs, and continues to run (I can see the RAW file being created and each loop I output data to a tracking table to see what tab it is currently on) and it goes through the rest of the variables/loops but it does not populate any of the other tabs even though I see the RAW file being populated each loop/etc.

Is there a setting or something on the server that could be causing this?  Or something else that would cause it to run correctly on my local machine and to run through on the server and finish (saying successfully) but not populating all the tabs?
LVL 16
brad2575Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Megan BrooksSQL Server ConsultantCommented:
Are your local machine and server both i86 architecture? I don't know, but there could be isues with Excel output on x64.
Have you tried configuring package logging? You can log information and progress events if you ask for them, and that might give you a clearer picture of what is happening. You might even want to log ALL events.
0
brad2575Author Commented:
There is an issue with Excel and 64 bit but that is why I am using the command line call to force it to use the 32 bit dtsx package executor.
0
Megan BrooksSQL Server ConsultantCommented:
Is your local machine x64 also?

Package logging is how I troubleshoot most production SSIS problems. Have you tried it with all events enabled?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

brad2575Author Commented:
No my local is not 64 bit.  I am running the logging and I am unable to see anything the last couple times I ran it, I am narrowing down what the log produces now so it may be easier to see.
0
Megan BrooksSQL Server ConsultantCommented:
The differing  architectures could have something to do with it.

Did you select all events on the "details" pane of the logging configuration window?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brad2575Author Commented:
Didnt really answer the question but appreciate the help, found another solution thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.