British formatted date in SSIS to Excel Data Flow Destination

Question

I have a package in SSIS, in the data flow I have a script component that is a data source, setting variables to an Excel Data Flow Destination.

With ScriptOutputBuffer
    .AddRow()
    .scriptRowID = 2
    .Filename = Variables.foundFile
    .RunTime = Now
    .Status = "found"
End With

Unfortunately the dates are coming out in the following format :-

7/2/2010 8:22:46 AM

The LocaleID on the script component and the Excel Destination Data Flow component are all set to English (United Kingdom)

Any help, or other things to check would be much appreciated.

EDIT: The script component was outputting the date in the DT_Date format. I changed it to DT_DBTIMESTAMP format and I am now getting the dates in the following format in Excel :-

2010-07-02 09:15:44.662000000

better, but still a little unfriendly when read by humans.

Answer 1

I think this needs to be solved in Excel - typically it stores datatime in locale-independent form (just like SQL Server) and displays according to Excel preferences or the formatting settings in the cell itself.

You could format it in the source and put it in a string data type - you would still require Excel to interpret your months and days the right way around...