SSIS 2008, Excel 2007 formatting excel columns pro

Question

I'm running into some formatting issues with exporting data to an excel 2007 spreadsheet.

I have a template file that has the columns formatted correctly, but when the data is exported it doesn't get the correct formats (currency, short date, etc.)

I'm thinking I have 2 options

Have excel autorun a macro. (hate that idea)

Format the data before it gets to excel and make it string data only. (I don't like that idea either as you are going to get those irritating data conversion arrows next to each cell)

I've used the IMEX=1 trick to get around the data import issues before, but is there a special trick for exporting?

thanks,

Answer 1

I spent ages trying to figurure this out.

What you need to do is have a hidden row of data in the spreadsheet you are exporting to.

SSIS will place the values into the spreadsheet, and assume the type is that of the row immediately above. So the data row needs to be the last row in the spreadsheet, and the data in each column needs to be of the type you want it to be.

You would think there was a better way of doing this. There isn't. Sorry :-)

Answer 2

So after much formatting and gnashing of teeth, I came down to the final solution. It appears that you still need the hidden row at the top for excel to use. That determines whether the data is formatted as numeric or string. If you want to do any more formatting, then you need to add in a script task to your SSIS package. I just threw the following lines into a .net script task at the end of my processing and it fixed it right up.

Please don't puke on the DailyWTF quality code ;-)

        Dim wb As Microsoft.Office.Interop.Excel.Workbook

    excel = New Microsoft.Office.Interop.Excel.Application

    wb = excel.Workbooks.Open("c:\reports\Report.xlsx")
    'This is the long ass Accounting Format'
    wb.Worksheets("Sheet1").Columns("E:E").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
    wb.Save()
    wb.Close()
    excel.Quit()

Answer 3

The solution is so simple it will make you scream. Just use the CREATE TABLE SQL command from a SQL task box in SSIS. Set the connection type to EXCEL. Here are the steps :

Let's use 'TEST' as the tab name on the spreadhseet.

1.) Copy the template file to a new file.

2.) Create a SQL task, set connection type to Excel and use the connection string to your newly created Excel file. Use the below code as a sample :

DROP TABLE TEST

GO

3.) Create another SQL task (using the same settings) and use the following code :

CREATE TABLE TEST

( Column1 Integer, Column2 DATETIME, Column3 NVARCHAR(50), Column4 MONEY )

GO

You can now start writing data. A couple of things I ran into is the I had to use the ` character instead of the ' for a comma. Also, I tried using both the DROP & CREATE commands in one SQL task but it didn't work for me, so I used two.