Like many DBA before me, I have battled SSIS Exports to Excel with valor and tenaciousness (word?). Sometimes I win, sometimes I lose (I think the score is SSIS 2 – Joe 5). I seem to remember having trouble exporting numbers to Excel in the past but it took me a while to figure it out again. So this time I am documenting it.
Assumes you have an existing package you are modifying. Works however with a new package destination as well.
- Right Click on your Excel Destination and click Edit
- Hit the New Button next to the Name of the Excel Sheet
- You should get a Create Table Window. Copy this CREATE statement to your favorite Text Editor
- Cancel out of the Excel Destination edit
- Find your actual Excel Destination file on the OS and delete it
- Now if you need to make changes to the worksheet you can using your text editor. Get the CREATE table setup like you want it. Make sure you define your number columns as Type Long and your Text Columns as LongText
- Right Click on your Excel Destination and click Edit again.
- You can now either use the CREATE TABLE that is generated by SSIS or you can use your CREATE TABLE Script you modified in Step 6
- If you have added new columns manually to your CREATE Table statement you will need to go to your source and fix that. You may get some validation errors that you will need to resolve.
- Click OK on the Create and then you should see your Sheet name in the drop down box.
This method will also remove any extra sheets in your Worksheet (Sheet1, 2, 3) and will create a new sheet based on the name in the CREATE TABLE Script.
JustSQL 