I am using SSIS 2008 R2.
I have created a Data Flow Task which exports the contents of an SQL table to an Excel template. The data exports with no issues (other than a cell colour issue which is another question!).
However, one of the columns is a date (which I know often causes problems!); the SQL column has a datatype of DATE.
For the Excel file the client wants the date in US format mm/dd/yyyy; the server is set to English(UK) and cannot be changed.
So, I have selected the column in the Excel template and set it to a custom format of mm/dd/yyyy. The problem is that when I open the file after the export has run, the dates show in the database format of yyyy-mm-dd. But, if you go into one of the cells and double-click it will change to the mm/dd/yyyy format.
Also, if you copy the column to notepad and then copy back, it shows the mm/dd/yyyy format.
The question is, how can I get it to show correctly in the first place? I've tried setting the localeID in the Advanced Editor to English(US) in the Excel Connection for the Data Flow Task and have also Refreshed the metadata, but none of this seems to make any difference.