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:
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?