0

I have the following problem. I'm exporting to an Excel 2003 file (has to be Excel 2003) from SQL Server through SSIS. It first creates a sheet through a SQL Task and then populates it with a SQL Data Flow. The Excel connection specifies that the first row has the column names.

The problem I have is that when the sheet only has one column, SSIS starts writing not in row 2, but row 3.

This is the SQL script that creates the sheet:

CREATE TABLE `Sheet1` (`Column` LongText)

And the script that populates it:

SELECT socialSecNum FROM Users

If I add a dummy column, with name ".", and in the DataFlow fill it with blanks, it doesn't skip that row, and starts writing in row 2.

The SQL Task script that creates the sheet in this case is:

CREATE TABLE `Sheet1` (`Column` LongText, `.` LongText)

It's the same SQL script that fills the Excel file in both screenshots. The output doesn't change, so there isn't a NULL value being inserted randomly at the beginning there.

What is going on? How do I avoid it? I can't have that "." column name there.

EDIT: Also note that it's not that the Excel files are dirty and that's why it leaves an empty row in row 2 because it thinks it's being used; the same file doesn't skip a row if I add a second column in the script.

EDIT2: I was asked to remove the pictures, sorry.

Heathcliff
  • 3,048
  • 4
  • 25
  • 44
  • @Matt I edited above – Heathcliff Jul 04 '16 at 17:53
  • I am hoping your screenshots are not actual social security numbers.... – Matt Jul 04 '16 at 18:18
  • @Matt No, of course not. I had to invent some new name for the column, and I imagined SSN would apply. – Heathcliff Jul 04 '16 at 18:19
  • 1
    :) whew I was thinking that was the case but just checking because you never know. I am trying to setup a test case to look at in one of my environments to see if I have any more ideas for you. Oh and I know you are saying their are no nulls in your data and while it would be weird if their were and they were ordered by differently have you tried explicitly removing them? SELECT socialSecNum FROM Users WHERE socialSecNum IS NOT NULL – Matt Jul 04 '16 at 18:21
  • @Matt Yes, I've tried that, there was no difference – Heathcliff Jul 04 '16 at 18:24
  • 1
    so using sheet1 for me doesn't work with a blank workbook, because it will create it as sheet11 instead. so I assume you where simplifying. So if I created other worksheets and with or without the period I have the exact same results which is no blank row. I am not able to replicate. Have you tried completely new Excel File? Is their anything else that could be affecting your record set? Have you turned on "Data Viewer" in the Data Flow Task and looked at the results? – Matt Jul 04 '16 at 18:34
  • @Matt it's a new Excel file. I first check if one already exists. If it exists, I erase it. Then I create a new one programatically with those SQL scripts. Keep in mind I'm exporting to Excel *2003*, so it's a .xls file, not a .xlsx file. Maybe it's that – Heathcliff Jul 04 '16 at 18:58
  • 1
    Yep I made sure to use Exel 2003 in my tests and wasn't able to recreate so there is obviously a piece of unknown information to me. The only thing I can think of is that something to do with your erasing/creation of the sheet. Is it possible to post the entire code of that? – Matt Jul 04 '16 at 19:10
  • @Matt The deletion is through a File System Task, just deleting the whole file. There's no script. – Heathcliff Jul 04 '16 at 19:14
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/116402/discussion-between-matt-and-heathcliff). – Matt Jul 04 '16 at 19:21

1 Answers1

1

I was finally able to replicate your issue of a blank row and your fix with the extra column. In the end I couldn't get back to not getting the blank row until I exported with the file actually open. Yeah that's right I got no blank row when the file was actually open in Excel while SSIS package wrote to it, which obviously is not a solution or a good one anyway.

CRAZY....

In all of the testing I did (a lot) I would say I got some inconsistent results using your SQL Task to create the table. If a worksheet with the same name already existed some times it would overwrite what was there but most of the time I would get new worksheet with an extra 1 on it. So when you are creating Sheet1 and it exists your table is created as Sheet11.... Because you are deleting the workbook all together you probably aren't seeing any of that weird behavior.

A quick search on the internet showed that this is a common issue to the 97-2003. So things you can do/try:

  • Switch to CSV but name the file with .xls extension, it will still open in Excel, have no formating etc. but user may get a warning when opening file.
  • Add the column then add another sql task to drop it after you populate it I wasn't successful with this but I don't do this in Excel so I may just not know a certain command.
  • Add another sql task to delete null rows, again I wasn't successful with this but I don't write queries against Excel very often.
Matt
  • 13,833
  • 2
  • 16
  • 28
  • I can't switch to CSV, since it's a client requirement. I've tried the other approaches with no results. I've tried writing the second column with nulls and just deleting the title (using a C# Script in SSIS), but though I'm able to do this successfully in my local computer, when I deploy it to production I get an error without any details. I noticed it has something to do with a library I use for this (Microsoft.Office.Interop.Excel), but I've not been able to solve it. – Heathcliff Jul 05 '16 at 22:35
  • 1
    Microsoft.Office.Interop.Excel is install when Excel is installed so unless it is installed on your server it wouldn't be present. I thought of that route to but I figured you weren't installing Excel or could you? – Matt Jul 05 '16 at 22:43
  • 1
    http://stackoverflow.com/questions/151005/create-excel-xls-and-xlsx-file-from-c-sharp. I guess according to this article their is a library available which might be an alternative to installing Excel but would still require installing components, but I have no idea as to sustainability, support etc. https://code.google.com/archive/p/excellibrary/ – Matt Jul 05 '16 at 22:45
  • Well, I solved it by changing the Excel connection to 2007. It generates a .xlsx file instead of the required .xls, to avoid showing the warning that Excel is trying to open a file with a different format than expected, but the client won't mind. Thanks a lot. – Heathcliff Jul 11 '16 at 13:45