Transformation Error

I am having an issue in simple transformations with date fields. The source I am getting this data from is an SAP Advantage Database ODBC 32-bit driver. The transformation takes this data and copies it to a MySQL database which I also connect to through ODBC. The SAP source date fields are formatted MM-DD-YYYY. When I pull the data through the ODBC connection in the transformation, the Reader object sees the data as YYYY-MM-DD HH:MM:SS.FFF. If I query this source with the 'Run SQL' tab, it shows the data in the correct MM-DD-YYYY format.

If I manually run the transformations, they run successfully with no issues. The correct MM-DD-YYYY date data is saved to the MySQL database. However, when using the execution agent to run these on a schedule, the transformations 'sometimes' fail with every record being rejected with this error: "Record Rejected: Field: Value '2024-08-27 00:00:00.000' is too long for field LAST_MOD". These transformations only fail on the LAST_MOD field, even though there are several date fields 

I find this strange as I have 10 different transformations scheduled every day, but it seems they randomly fail. Somedays they will all run without issue. Sometimes one of the transformations will fail on Saturday but then succeed on Sunday. All 10 of these transformations have date fields. Some of them have never failed, and some fail frequently. Sometimes they will fail every time until I delete the scheduled event and go into the transformation and delete the transformer and writer objects and recreate them.

I tried using the Date Reformat transformation, but then I would get errors about field length every time I ran it.

I've attached an example where the execution agent ran a transformation this morning and it failed. I then ran the same transformation manually and it succeeded. Screenshots and CSV of some example data with identifying information removed has also been attached.

Hi Nick

Welcome to the forum

1 Our software  is using standard ODBC format for loading data into DATE fields

YYYY-MM-DD HH:MI:SS.FFF

If your data already in this format there is not need to perform additional data transformations it should just work
 If you data is in different format for example dd/mm/yy you can use Date format transformation function to covert it into standard ODBC format

2 Regarding why our software is not working when run from the agent.

Quite often it is related to the user settings. EG: You design the transformations as JONN but you run the AGENT as PETER.
We always recommend using same user name.

That does not necessary meant that it is the source of the problem you are having.

3 Our software works directly MySQL there is no need to use ODBC connection. I've tested both direct connection and ODBC connection both of them worked fine.

Unfortunately you did not tell us the version of ODBC driver. We used 8.0.0.36 unicode driver for testing.

4 Some MySQL  ODBC drivers have bugs which might lead to the errors you are having

stackoverflow.com/questions/38307227/odb...from-32-bit-odbc-5-1

stackoverflow.com/questions/37032330/mys...ng-datatype-to-adodb

5 Re  '2024-08-28 00:00:00.000' is too long for field DATE

The table creation script you sent to us has field called 'DATE' 

It would be interesting to see the screnshot of this filed definition. (Data writer=> show definition)

  

Hi just wanted to follow up that I solved my issue. It was the ODBC driver connection to the MySQL server. I originally didn't use the direct connection because I was having some issues with SSL. But after resolving those and switching my transformations to the direct connection, I haven't had any more problems. Thank you for your help.

Thank you for letting us know. 

I am glad it is working now