If I have a column of type DT_DBDATE or DT_DBTIMESTAMP, and I write the data to a file using a Flat File Destination component, the resulting date formatting will YYYY-MM-DD HH:MM:SS. I've experimented with changing the package locale and changing the datatype in the Flat File Connection Manager, but these don't seem to have any effect.
What determines this format and is there any way I can change the default formatting?
PS. I know I can use Data Conversion and Derived Column components to change the data types/formats. The point is we have +150 packages and get too many errors when someone forgets to do the conversion or does it incorrectly.
We're using SSIS 2012.
I had a similar issue while using SSIS 2008 with SQL Server 2008 R2. If you are trying to generate a CSV file, I would recommend first, at the database level, to store the value in the desired format. This means, for instance, if you want to display a column where the current date is in the format YYYY-MM-DD, then you should use the appropriate code to store that value. Using T-SQL will be: convert(varchar(10), cast(GETDATE() as date), 126). Double check that the value was stored in the desired format by reading the table. Now it comes the tricky part: no matter what you are using in SSIS for transforming the dates, if you open the CSV using Excel, then Excel is going to display the dates using the format was set for the operating system. Don't panic, just don't save that file when you want to close it. Instead, if you open the CSV using Notepad or Notepad++, you should be able to see the date columns right in the format you used for storing them in the database.