I have a strange problems with dates.
I’m running an ETL process on Data Services 4.2 SP7.
Source and target DB are both SQL Server 2017.
When there is a date like 01-01-01 (default dates used in the source DB) or a date in the year 2100 the ETL throws this error:
- “ODBC data source error message for operation : <[Microsoft][ODBC SQL Server Driver]Fractional truncation [Microsoft][ODBC SQL Server Driver]Datetime field overflow>.”
- “Function call <raise_exception ( STOPPED DUE TO AN EXCEPTION IN THE CURRENT JOB ) > failed, due to error <50316>: .”
I have 3 environments (they are 3 different servers with 3 different installations): development, test and production. This error only happens in the test environment. Dev and Prod works without any issues with those dates.
The connections to the source and target databases are all made in the same way, using SQL 2014 compatibility (the highest available in this version of DS).
I’ve identified the dates as the source of the problem because if I update them to the current year everything works.
The query transform object does not apply any transformation to these dates, it just copy them to the target table. In both source and target tables the datatype is Datetime2(7), and the field in the Query Transform is treated as Datetime.
Is there anywhere an hidden configuration I must check to have these dates processed correctly as in the other two servers? I cannot find anything…
Thank you in advance.