How to avoid empty rows in SSIS Excel Destination?

Question

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

Answer 1

^{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.

• Henry Lee: 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.
• Jon Seigel: @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.
• Henry Lee: 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.
• Jon Seigel: @Henry: I would definitely call that a win. :)
• Thronk: 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/…

Answer 2

I ran into similar issue recently, what worked for me was to trick the Data Flow Task by adding a File System Task to copy an empty file with same table structure. Here is my 3 step solution:

1. I created the Data flow task with the excel destination and parameterized connection string.

2. I created a folder which I named "Default folder" and copied the empty excel destination created in step 1.

3. I added a File System Task to copy the default file from [Default folder] into my designated destination folder with an overwrite if the file exists.

Answer 3

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.

Answer 4

I had same issues with the blank rows in excel being inserted. I ended up creating a brand new excel file with the same headers. Somehow the old file had some formatting in place that was not visible to me that I could fix in order to remove the blank space.