PCK.AUTOMIC_UTF-8.MIG-STEP03.SUPPORT.NOILM.SRC pack failing

[DeepakChatrath604743]

When we are running the automic pack for UTF8 conversion as a pre requisite step for upgrading from V21.05 TO V24.3 ,below  job  

PCK.AUTOMIC_UTF-8.MIG-STEP03.SUPPORT.NOILM.SRC

is failing with below error :

2025-04-03 09:01:26 - U02000005 Job 'PCK.AUTOMIC_UTF-8.MIG-STEP03.SUPPORT.NOILM.SRC' with RunID '1125679504' is to be started.
2025-04-03 09:01:26 - U02012001 Successfully established connection to 'server name/borisr0' with user 'UC4DB'.
2025-04-03 09:01:26             Oracle
2025-04-03 09:01:26             Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
2025-04-03 09:01:26             Version 19.26.0.0.0
2025-04-03 09:01:26 - U02012014 JDBC Driver version: 19.13.0.0.0
2025-04-03 09:01:26             create table uc_migration (Mig_run number, start_time date, end_time date,
2025-04-03 09:01:26                                        AH_IDNR_START number, AH_IDNR_END number,
2025-04-03 09:01:26                                        MELD_IDNR_END date,
2025-04-03 09:01:26                                        XAO_IDNR_END date,
2025-04-03 09:01:26                                        schema_name varchar2(128),
2025-04-03 09:01:26                                        transfered char(1))
2025-04-03 09:01:26 - U02012003 0 row(s) affected
2025-04-03 09:01:26             alter session set nls_date_format = 'YYYY-MM-DD'
2025-04-03 09:01:26 - U02012003 0 row(s) affected
2025-04-03 09:01:26             insert into uc_migration values (1, '1997-11-16', '2025-03-20', 6227804, 1097758037, '2025-04-02', '2025-03-31 00:00:00.000', upper('borisr0'),'N')
2025-04-03 09:01:27             ORA-01830: date format picture ends before converting entire input string

[Martin Uferbach]

It's planned to release version 1.0.6 of the UTF-8 migration action pack today. According to the fixing team, the date format issues you mentioned should be solved/covered in this version, actually already in 1.0.5. 

------------------------------
Product Owner for Automic Automation
Broadcom
Austria
------------------------------

[Markus Embacher]

Hi,

seems you are running into an issue which has been fixed with the latest release 1.0.5 of the UTF-8 migration action pack. Please download the latest version and try again. 

The error is because the expected date format is YYYY-MM-DD, but the insert into statement is trying to insert 

2025-03-31 00:00:00.000

which does not match the date format. This has been fixed in PCK.AUTOMIC_UTF-8.MIG.SQLI.GET.GET_XAO_IDNR_END.SRC by enforcing YYYY-MM-DD.

Regards, Markus

[Wendy Copeland]

Try changing the Date Output Format in PCK.AUTOMIC_UTF-8.MIG.SQLI.GET.GET_XAO_IDNR_END.SRC Variable to yyyy-mm-dd

[Michael A. Lowry]

For those who are interested, I collected some useful links and information about the UTF-8 migration tool.

Documentation

Migrating AE Databases to UTF-8

Downloads

UTF-8 DB Migration Action Pack

Fix list

Description	Version	Release date	Problem ID
A bug was fixed where the data was moved into an incorrect tablespace when using ILM in Oracle.	1.0.6	2025-04-04	36088016
The email notification functionality was enhanced by sending more often emails	1.0.6	2025-04-04	33687160
A problem has been fixed that prevented the migration of data when the source system's version was lower than 21.0.7.	1.0.6	2025-04-04	36135312
Resolves an issue where the XAO data type return value changed from text to DATE format	1.0.5	2025-02-07	35763154
The DB migration tool can also be used with AE version 12.3 again.	1.0.5	2025-02-07	35758592
We use explicit date format and do not rely on database settings date format or language, fixed also using wrong date conversion for the start date.	1.0.5	2025-02-07	35573282
Improved the SQL query for migration in the DB migration tool for Oracle	1.0.5	2025-02-07	36081038
The tables are first checked to see if they require an IDENTITY_INSERT setting, and then the insert statement is generated accordingly.	1.0.4	2024-09-16	35440097
We added in email that we tested our generated SQL only with database vendors tool. These SQL scripts have been verified using official database tools. If you're using third-party tools, please adjust the SQL queries as needed. We recommend the following tools, which we used for testing: Oracle: SQL Developer DB2: IBM Data Studio MS SQL Server:  SQL Server Management Studio (SSMS)	1.0.4	2024-09-16	33669136
Migration failed due to different date formats. We now use the ISO 8601 Format TO_DATE('2024-08-30', 'YYYY-MM-DD').	1.0.4	2024-09-16	35146158
We use explicit date format and do not rely on database settings date format or language	1.0.4	2024-09-16	35440211