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?
 
Megan BrooksConnect With a Mentor SQL 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
 
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
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
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
 
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
 
brad2575Author Commented:
Didnt really answer the question but appreciate the help, found another solution thanks.
0
All Courses

From novice to tech pro — start learning today.