SSIS Export to Excel Numbers Stored as Text

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.

  1. Right Click on your Excel Destination and click Edit
  2. Hit the New Button next to the Name of the Excel Sheet
  3. You should get a Create Table Window. Copy this CREATE statement to your favorite Text Editor
  4. Cancel out of the Excel Destination edit
  5. Find your actual Excel Destination file on the OS and delete it
  6. 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
  7. Right Click on your Excel Destination and click Edit again.
  8. 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
  9. 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.
  10. 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.

Advertisements

One thought on “SSIS Export to Excel Numbers Stored as Text

  1. SQL Dude, i wished this worked but it appears it doesn’t. If your intent is to get numbers to show up as numbers in Excel instead of “number stored as text”. I am battling with SSIS exporting numbers to Excel myself. The only solution i have found so far is to add a dummy row to the Excel sheet containing dummy data of the correct data type, the odbc driver appears to inspect the first 8 rows of data and determine a data type based on the data already in the sheet, and if it cannot determine a datatype it defaults to text. So if i populate a dummy row with sample data the numbers are numbers in Excel (not numbers stored as text). Problem is this dummy row of data can throw off totals in the spreadsheet unless we manually open the spreadsheet and delete this row. If i misunderstood your method i’d like to know as i find this behavoir of excel to be very annoying and the workaround is ‘unsatisfying’, and if your solution worked would be awesome alternative.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s