Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Does anyone have a way to avoid empty rows when using SSIS to export to Excel. Here's a simple example of one data flow task:

OLE DB Source:

OLE DB Source:

Data Conversion (to handle the annoying UNICODE / NON-UNICODE deal):

Data Conversion

The end result is either of the two below depending on value of "FirstRowHasColumnName" in the Excel Connection Manager. Note, the blank rows.

output 1

output 2

share|improve this question

2 Answers 2

I tested this in SSIS from a 2008 R2 install and Excel 2010.

Using the Excel 97-2003 output format in the connection manager, I was able to reproduce the case where there was the header name in the first row, then a blank, then the data. But changing the "First row has column names" had a different effect for me: I still got the header row, but no blank.

Using the Excel 2007 format, I wasn't able to suppress the header row either, though in both cases, it didn't output a blank row.

You didn't say if you want the header row or not, or which output format you're using; based on my testing, you're using 97-2003, so all you might have to do is switch to the 2007 format.

Attaching a Data Viewer just before the Excel Destination shows only data rows, so the extra blank is being added by the Destination component. So if the Destination isn't configurable to get rid of the blank, you're out of luck.

Suffice to say, though, since the behaviour of this component seems very buggy and the whole setup process to even get this to work at all was such a pain, I would strongly consider switching to a more stable component like the Flat File Destination to output CSV, which you can open directly in Excel, too.

share|improve this answer
    
Thanks Jon. Yes, I'm using 97-2003. When I select 2007, I get "The Microsoft.ACE.OLEDB.12.0 provider is not registered on the local machine." My test machine is Win7 with SQL 2008 R2. Agreed on the CSV format, I've already suggested that, but the client wants Excel. –  Henry Lee Jun 15 '13 at 23:41
1  
@Henry: Yep, same error I got and I'm running Win7 x64. I had to download some redistributable components from here to get it to work -- IMPORTANT: only the 32-bit version of the download will work. –  Jon Seigel Jun 16 '13 at 14:48
    
I did the same on my test machine, however wasn't real keen on installing anything on the client's server. In the end, we were able to convince the client to go with CSV. Then I was able to use Powershell, soooo much easier than SSIS. –  Henry Lee Jun 17 '13 at 14:15
    
@Henry: I would definitely call that a win. :) –  Jon Seigel Jun 17 '13 at 22:03
    
Regarding the "The Microsoft.ACE.OLEDB.12.0 provider is not registered on the local machine." error - you can fix that by installing the AccessDatabaseEngine microsoft.com/en-us/download/details.aspx?id=13255 as seen in this SO issue:dba.stackexchange.com/questions/49703/… –  Thronk Sep 13 '13 at 17:50

I ran into the same issue (SSIS 2012 and Excel 2007 format) I had my original header line, a bunch of empty lines and then the exported SSIS data.

I resolved the issue by selecting the whole spreadsheet except for the title row and by deleting the selection (right-clik and choose delete). This must have removed any left-behind formatting of blank cells. Then I put my selection to cell A1, saved the empty xlsx file and now SSIS starts writing the output on line 2.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.