Phil - if you add a CHAR(9) to the beginning of a field in Excel it forces that field to be interpreted as a text field and not a numeric field, but that is only when the data in imported into Excel from a comma/tab delimited file. If SSIS is exporting directly to Excel - then you should be able to define the column types in the Excel 'table' as specific data types.
Ah, makes sense, thank you.
The OP suggested that the CHAR(9) was 'added' – made it sound like it was appended to the column, rather than being spliced in at the beginning.
Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this
If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.