question

JohnConnor-1052 avatar image
0 Votes"
JohnConnor-1052 asked JohnConnor-1052 answered

Delta copy from Oracle to Azures SQL: ORA-01843: not a valid month

Hi,
I'd like to run a delta copy using the new metadata-driven copy task that is currently in preview. In my Oracle source table I have a column of data type TIMESTAMP which contains values like this: 2021-06-30 23:59:59.100000
The target table shall be auto-generated on Azure SQL and the automatic mapping within the menu shows me that a datetime field will be generated for this column. I finished the whole guided menu for the metadata driven copy task without problems, i.e. I also ran the 2 SQL commands provided at the end to generate the MainControlTable and also the stored procedure.
However when I start the debug run, I get an error and clicking through various processes the original problem is shown:

Failure happened on 'Source' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [22008] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-01843: not a valid month,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=System.Data.Odbc.OdbcException,Message=ERROR [22008] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-01843: not a valid month,Source=msora28.dll,'

I am not sure which date could cause the problem. For specifying the "water mark column value start" one can only select a date from the date picker, which I set to a date before my first MODIFIEDDATE values start. Unfortunately it is not possible to specify another column which contains the UNIX timestamp.

For testing the validity of the origin table I also tried running a simple, traditional copy job with ADF to create a target table in the destination and it works perfectly by copying all data to the created target table.

azure-data-factory
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@JohnConnor-1052
hi!
If it becomes a hint even a little
one of considering, Difference in data type
●Oracle source table a column of data type TIMESTAMP
=YYYY-MM-DD HH24:MI:SS.FF6
●The target table that a datetime field
=YYYY-MM-DD HH24:MI:SS

Similar question
https://community.powerbi.com/t5/Power-Query/ORA-01843-not-a-valid-month-when-use-to-date/td-p/1723635

0 Votes 0 ·

I don't see that SQL Azure's datetime data type would have no decimal places. I see there is a field "datetime format" in the column mappings of ADF, but had no luck by specifying formats there.

1 Vote 1 ·

Thank you for your comment.
"To date from something" errors that
It seems that there are several types.
I hope it will be a hint.

reference data type

https://docs.oracle.com/cd/E96517_01/nlspg/datetime-data-types-and-time-zone-support.html#GUID-7A1BA319-767A-43CC-A579-4DAC7063B243
TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF'

https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver15
datetime
n* is zero to three digits, ranging from 0 to 999, that represent the fractional seconds.

for example
The format of the month is
M, MM, MMM, MMMM
https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings#month-m-format-specifier

0 Votes 0 ·

1 Answer

JohnConnor-1052 avatar image
1 Vote"
JohnConnor-1052 answered

In my opinion there is a reason that this tool is in preview, because my problem was the result of different timestamp formats of 1) the menu defined start timestamp 2) the timestamp generated by the query in getMaxWatermarkValue in two ways: a) by using it in the delta query within the copy task and for the stored procedure that writes into the watermark table, so I had to harmonize the formatting like this:

 • date format in the watermark table is like this "2021-06-27T00:00:00.000Z" but needs to be manually set like this "2021-07-29 18:42:01.387704"
 • query in getMaxWatermarkValue has to be like this
 select to_char(max(@{json(item().DataLoadingBehaviorSettings).watermarkColumnName}), 'YYYY-MM-DD HH24:MI:SS.FF6') as CurrentMaxWaterMarkColumnValue from "@{json(item().SourceObjectSettings).schema}"."@{json(item().SourceObjectSettings).table}" 
 • query in delta load has to be like this

 select * from "@{json(item().SourceObjectSettings).schema}"."@{json(item().SourceObjectSettings).table}"
                     where @{json(item().DataLoadingBehaviorSettings).watermarkColumnName}
                     > to_timestamp(@{variables('WatermarkValueQuoteChar')}@{json(item().DataLoadingBehaviorSettings).watermarkColumnStartValue}@{variables('WatermarkValueQuoteChar')}, 'YYYY-MM-DD HH24:MI:SS.FF6')
                     and @{json(item().DataLoadingBehaviorSettings).watermarkColumnName}
                 <= to_timestamp(@{variables('WatermarkValueQuoteChar')}@{activity('GetMaxWatermarkValue').output.firstRow.CurrentMaxWaterMarkColumnValue}@{variables('WatermarkValueQuoteChar')}, 'YYYY-MM-DD HH24:MI:SS.FF6')

May this be helpful for somebody, it cost me a lot of time to get this right...

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.