Day and Year got swapped SSIS and SQL Server

Question

I got strange issue between SSIS and SQL Server.

I've developed SSIS to read CSV flat files and upload the files to SQL Server. Everything is working great and perfectly. The strange issue is that out of 1000 records that SSIS is importing 200 records have an issue with date where the date data are getting swapped the day became in the place of the year and the year in place of the day.

This is an example of perfect row and problematic row

Perfect row:

2018-12-17 09:05:00.000

Problematic row:

2017-12-18 09:05:00.000

Anyone faced the same issue or knows how to solve please assist.

Thanks

Answer 1

Just try to convert the date format while loading into tables in ssis/after loading into table just update the records into a proper datetime format using convert function in backend.

In Csv itself getting mixed format in the sense just push it into staging table and split up the problematic records and correct the source data.

Answer 2

The following steps supported me to sort out the issue:

1. Set the PC format to be dd-mm-yyyy hh:mm:ss
2. Open the CSV file and try to expand the columns which is having the issue.
3. Close the CSV file and save the changes.

and run the SSIS.

Hope that will help you too