Add IMEX=1; into the connection string, this tells excel to read everything as text no matter what. Then you can do your data conversions later in the package. Check out
connectionstrings.com, a great developers resource.
Thanks Daniel. Interestingly the IMEX property for the Excel connection is already= 1. However I googled IMEX=1 and found that I will need to change the registry settings for Excel.. Sledge hamer to crack a nut..This can cause issue going forward. Either way when I look at the connection previewer for Excel with IMEX at 1 in the conection string the columns with text data still show as null. Do'h...
Found a way to do this. Thanks to all for your help. Just modify the reg keys below: Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows (Change the value for TypeGuessRows to zero) Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes (change this to Text but it should already be text) Also ensure IMEX=1 in the connection string for the Excel connection properties. I didn't need to change the data type afterwards, it retains the DTSTR (string type) and the previewer now has the correct values not just the nulls.