Hi All,

I am exporting data from SQL Servre to EXCEL using Excel Destination. While exporting to Excel the numbers are automatically convert to text.

I want it to be number. I have tried changing the properties data type of column from advance editior and also tried the data conversion task but no luck

please let me know if there is any work around to fix this issue.

Thanks for your help!

Select the type of source as number (You can use the properties window to set this). Ensure the number column you are going to output contains numbers instead of formatted numbers like (12,34).

SSIS scans the input to determine the type. Hence I strongly suggest to validate the input.

Hi Dhganesan,

This is a known issue and the root cause is that Excel identifies data type for a column based on sampling of top few rows (8 by default). Generally, we work around this issue by using an Excel template file and insert a dummy row with the format as we are excepting for the rest of the cells. Once the data is export to the Excel file successfully, we can delete the dummy row from the Excel file manually. We can also insert a Script Task to the package to delete the dummy row. For more information, please see:

• http://sqlserversolutions.blogspot.in/2011/10/numeric-gets-converted-to-text-in-excel.html
• http://social.msdn.microsoft.com/Forums/sqlserver/en-US/ba555144-6b6e-410b-ba70-e05f59c79128/ssis-problem-while-exporting-numbers-stored-as-text-in-excel?forum=sqlintegrationservices
• http://social.msdn.microsoft.com/Forums/sqlserver/en-US/7f79cfde-2f1f-4cad-94f8-07397b998296/delete-data-in-excel-using-openrowset?forum=transactsql

Regards,
Mike Yin

If you have any feedback on our support, please click here