SSIS to Excel Date Format Issue

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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dung DinhDBA and Business Intelligence DeveloperCommented:
Can you format this column when extracting data?
CONVERT(varchar(10), column_date,101) --> 101 will format to mm/dd/yyyy
Mark WillsTopic AdvisorCommented:
If you double click on the flow task, you can double check the metadata - if it is accessing TABLE then it will pick up the underlying data property.

If already converting to string... Either change the datatype of the [Date] column in the Advanced editor to String OR ... you could drop the DB source and recreate as an SQL Command and select in the desired formats as if it were T-SQL then the convert(varchar(10),[date],101) will work.

So double check that Arrow to see the metadata.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Karl_markAuthor Commented:
Thanks both.
I added a column to the data source using the convert command and it now outputs in the mm/dd/yyyy format.
I had to alter from VARCHAR(10) to NVARCHAR(10) as using the former gave an error with unicode/non-unicode issues, but other than perfect.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.