I'm using an Excel file as a source, and I want to avoid some of the datatype issues commonly found with Excel files.  Is there a way to use a script task, or any other method, to automatically convert all of the columns in the Excel file to Text datatype (on every sheet)?  I would basically copy my source file each time I run the process, run the script to convert all the columns, then load the data from the converted file.

Hi Tim,

Just use the Data Conversion Task.

But the issue is perhaps due to shallow (8 records by default depth) data sampling: Why SSIS always gets Excel data types wrong, and how to fix it!

You can try setting IMEX=1 if intermixed data types are coming . Another option is to change registry setting for TypeGuessRows to scan more rows to determine on data types.

http://microsoft-ssis.blogspot.ae/2011/06/mixed-data-types-in-excel-column.html

If you still want to change datatype while extracting from excel you can use data conversion or derived column tasks for it

https://www.simple-talk.com/sql/ssis/importing-excel-data-into-sql-server-via-ssis-questions-you-were-too-shy-to-ask/

Hi Tim,

Check the commercial COZYROC SSIS+ library. It includes Excel adapters which doesn't have data type issues like the regular Excel adapters.