March 18, 2015 at 5:40 pm
I have an SSIS package that will export more than 65000 rows to Excel. So I have been trying to output to an Excel 2007 file.
Everything works fine on my local machine, but things get strange, and only partially work, whenever I deploy the package to the server and run it via a SQL job.
Here is the pertinent data:
1. Windows Server 2008 R2 64 bit
2. SQL Server 2008 R2
3. The job is configured to run as 32 bit
4. I have tried both SSIS Excel Destinations and OLE DB Destinations.
5. My report file is named with a *.xlsx extension (I have tried setting the Extended Properties to "Excel 12.0 Xml")
6. The largest column is 150 characters.. all strings are unicode and I also have some dates and money fields.
7. At one point I could not even acquire a connection to the spreadsheet until I installed this: http://www.microsoft.com/en-us/download/confirmation.aspx?id=23734
So here is the problem:
I can only get about 5,000 rows to export. (5162 to be exact). Any more than that and nothing exports at all and yet *no* errors are logged. It has nothing to do with the data in row 5163 because I can load then next 5000 or so rows just fine by changing my query to skip the first 5000.
So literally, if I do a "SELECT TOP 5162 *"... all is good... if I do even one more row, the spreadsheet is empty and no errors are logged.
What gives? Is there some sort of data size restriction when exporting to Excel 2007? I am only exporting maybe around 25 columns so it is not a huge amount of data.
March 19, 2015 at 2:40 am
There shouldn't be a size restriction.
Try logging almost everything, and see how many rows the data flow writes to the destination.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 19, 2015 at 9:50 am
I have a standard logging and configuration setup for all my SSIS packages. Usually I only log Errors, Warnings, and any ScriptTaskLogEntry events, but it was trivial to turn on logging for every event type.
After making the change, I ran the package twice. Once doing a "SELECT TOP 5162 *", and once doing a "SELECT TOP 5163 *". Keep in mind that the 5162 number seems to be based on data size, not row count... because the number of rows that I can successfully push to the Excel spreadsheet changes if I grab a different subset of the records instead of just the first 5162 rows.
Both runs returned the exact same number of log rows (531 to be exact). They appear to be identical except for one event:
The 5162 run logged this event:
Event: OnPipelineRowsSent
Message: Rows were provided to a data flow component as input. : : 486 : OLE DB Source Output : 469 : OLE DB Destination : 482 : OLE DB Destination Input : 5162
The 5163 run logged this event:
Event: OnPipelineRowsSent
Message: Rows were provided to a data flow component as input. : : 486 : OLE DB Source Output : 469 : OLE DB Destination : 482 : OLE DB Destination Input : 5163
No errors were logged in either case. Just as before, the spreadsheet in the 5162 run contains all the data while the spreadsheet in the 5163 is completely empty.
March 19, 2015 at 10:10 am
Oh... and just one last piece of information.
I have this same issue even if I just hard-code the values.. the only thing that changes is the amount of rows that I can successfully export into the spreadsheet.
e.g. -
Instead of SELECT Field1, Field2, Field3, ... FROM Table1
I tried SELECT CAST('Bogus' as nvarchar(50)) AS Field1, CAST(0.0 as money) AS Field2, ... FROM Table1
Effectively eliminating any specific data as an issue. The second query returns the full number of rows, but each value is just hard-coded for testing.
The only thing that changed was that I could return more than 5162, but the number was still limited to less than 7500. Any more than that and the spreadsheet is empty with no errors logged. Obviously this is nowhere near the 95,000 I was hoping to return.
Clearly there is some sort of limit at play here, but I don't know what would be imposing it.
March 19, 2015 at 10:49 am
Do you create a new excel file?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 19, 2015 at 11:04 am
Thanks for taking your time to respond.
No, I do not create a new spreadsheet. I have a "File System Task" which does a copy file of a template xlsx file that I created.
This template is nothing more than just a formatted header row. It was created from scratch using MS Excel 2010 saved as an "Excel Workbook (*.xlsx)" file
Again.. it all works great with a limited amount of data (or if I run it from my local machine). I just reach some sort of limit when I run in on the server and instead of having data in my spreadsheet, it is just an empty copy of my template.
Brian
March 20, 2015 at 11:02 am
I have solved the problem after a lot more digging around...
Here is the post that actually solved it for me:
It turns out that it was something related to saving a buffer file to the "C:\Users\Default\AppData\Local\Microsoft\Windows\Temporary Internet Files" location when the data was large enough.
I simply gave my job domain account permission to "C:\Users\Default" and voila!.. everything worked.
-------------------------------
just as a quick fyi, here is another post describing what I see, but did not actually solve my problem:
http://stackoverflow.com/questions/24213286/ssis-excel-destination-blank
Unfortunately the comment regarding the error handling was not my problem, but I am posting it here in case it helps someone else.
March 20, 2015 at 11:14 am
bwrightdev (3/20/2015)
I have solved the problem after a lot more digging around...Here is the post that actually solved it for me:
It turns out that it was something related to saving a buffer file to the "C:\Users\Default\AppData\Local\Microsoft\Windows\Temporary Internet Files" location when the data was large enough.
I simply gave my job domain account permission to "C:\Users\Default" and voila!.. everything worked.
-------------------------------
just as a quick fyi, here is another post describing what I see, but did not actually solve my problem:
http://stackoverflow.com/questions/24213286/ssis-excel-destination-blank
Unfortunately the comment regarding the error handling was not my problem, but I am posting it here in case it helps someone else.
Great, glad you got it solved and thanks for posting the solution.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply