1

experts! I've got two similar Excel files (xlsm) as templates. Both have sheets with Date column. Visible format for both files when use Excel is "10-Aug-20". But when I read these files with SSIS process with Script Component Source using Microsoft.ACE.OLEDB.12.0 with "IMEX=1"... ta-da... some I see as expected, but some are 10.08.2020 00:00:00 This causes me a lot of pain because I will process files from both US (MM/dd/yyyy) and German (dd.MM.yyyy) date formats and would like to have locale-independent date format to process dates same way. How can I force excel to give or ssis to read a correct date format.

Any suggestion how to see both files same programmaticly is most wanted and highly appreciated!

Alexander Chernov
  • 31
  • 5

1 Answers1

0

You could try to use the script component (as Transformation) to transform the data by using DateTime.TryParseExact

  string dateString = "10-Aug-20";
  string format = "dd-MMM-yy";
  DateTime dateTime;
  if (DateTime.TryParseExact(dateString, format, "en-US",DateTimeStyles.None, out dateTime))//if (DateTime.TryParseExact(dateString, format, "de-DE",DateTimeStyles.None, out dateTime))
        {
            Console.WriteLine(dateTime);
        }
LONG
  • 4,490
  • 2
  • 17
  • 35