SSIS Excel Source importing individual date columns in different formats
<p>I've looked at a number of similar questions but can't find anything relating to the specific issue of different date columns being read in different formats.</p>

<p>My source file (xlsx) has columns Date1 and Date2 - both of which are stored within Excel in Date format. </p>

<p>Using the Excel source (Locale = English (UK)) - the preview shows the data in the way I would expect to see it within my Locale (DD/MM/YYYY) for both columns.</p>

<p>For 'reasons' I want all my columns to be processed as text, so within the Excel source advanced editor I've made all the output columns Unicode String. When I place a data viewer straight after the Excel Source control, Date1 is in DD/MM/YYYY format, but Date2 is in MM/DD/YYYY format! (this continues down the flow to my destination, I'm just using the viewer to prove the issue occurs from the initial source loading)</p>

<p>How is this happening? I've tried IMEX=1 in the connection manager connectionstring but that had no effect.</p>

<p><strong>EDIT</strong> - more details
The first few rows of source data for Date1 are blank, whereas Date2 has values of 12/10/2015.</p>

<p>By about row 40 of the source, Date1 is populated with 03/05/2011 and Date2 17/02/2016 - here in the data viewer Date1 displays as 03/05/2011 but Date2 displays as 2/17/2016 - note the leading 0 is truncated in this case but not Date1? </p>