Exporting data to Excel sheet without writing column names in SSIS

Question

I have this problem. I have to create an Excel 2007 file dynamically. To do so, I have an Excel connection with the "First Row Are Column Names" check uncheked. Then I created a SQL Task with that Excel connection and the following SQL Statement:

CREATE TABLE `MySheet` (`MyColumn` LongText)

And then I have a Data Flow Task that fills the sheet with a single-column stream of data.

The problem is that even though I unchecked the "First Row Are Column Names" check, the exporting still writes "MyColumn" in the first row. I need to be able to export the data from the first row onward, without any other thing being written on it.

This is a problem because there's the possibility that the Excel file must be overwritten by the SSIS package on a different run. Since we don't have deletion permits in the folder where the file is created, I need to delete the contents of the sheet from the previous run. I manage to do this using a SQL Task and the Excel connection with the statement:

DROP TABLE [MySheet]

This manages to erase every row without deleting the sheet. But, since this task is followed by the Data Flow Task instead of the sheet creation task (because the sheet is still there), when the Data Flow Task fills the rows again, it leaves the first row empty. It doesn't re-write the column name.

This difference is what I'm trying to avoid.

Is there any way to avoid this?

Thanks

That makes more sense. Quite honestly I havent encountered such an issue before. When I dynamically created the excel files, I had a task to move the old ones to an archive folder, then create a new one and fill it with new data. But haven't done the rewrite on the existing one. Let me try and read something on this. — rvphx
Instead of Drop table can you try DELETE * FROM [mySheet$] if you havent done so already? — rvphx

rvphx rvphx · Accepted Answer · 2017-01-20T16:29:26

Short and sweet answer is no. Not possible. It will always have to use something to make it distinct. What you can do is, use a small poweshell script to open the excel file, run a small macro to delete that first row and then save it and close it. There are a lot of powershell scripts that can help you with this.

Exporting data to Excel sheet without writing column names in SSIS

1 Answers