How do I get stop Excel columns from changing from TEXT to GENERAL when my data task within SSIS writes from an OLEDB to my Excel template file?

I have an SSIS package (2008 R2) where I have a SQL Server source writing to an Excel file. The Excel file is a template and all of the columns are TEXT. After the data is written to the Excel file the cells that now contain data have changed from TEXT to GENERAL. How do I prevent this so that all cells remain TEXT.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
You could run a simple macro to convert the cells back to Text

Option Explicit

Sub FormatAsText()
    ActiveSheet.Cells.NumberFormat = "@"
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marketoneDevAuthor Commented:
Hi Roy, I created the macro and it runs in the open event of the workbook. However, the file gets saved with a .xlsm extension which works fine outside of SSIS but my package is uses a data flow task to write to the Excel file from a SQL Server database.  Any suggestions as to how to get around this problem.  Thanks.
Roy CoxGroup Finance ManagerCommented:
If SSIS doesn't recognise  the xlsm format then you could put the code into an addin and manually run the code when required.
marketoneDevAuthor Commented:
The suggestion to run a simple macro to convert the cells back to Text is what I needed. However, I cannot run this manually as it is part of a scheduled SSIS package and SSIS does not recognize an .xslm file. I'm going to try to use a script task within SSIS using c# to see if that will work.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.