When you configure a mapping to read date, timestamp, or datetime information from a string column and process the data with the default date/time format to write to Google BigQuery target, the mapping fails with the following error:

[ERROR] The Secure Agent failed to run the full SQL ELT query due to the following error: [Invalid timestamp: '12/31/1972 00:00:00.000001']

To resolve this issue, set the JVM option -DHonorInfaDateFormat=true for the Secure Agent.

Perform the following steps to configure the JVM option in Administrator:

Select
Administrator
Runtime Environments
.
On the Runtime Environments page, select the Secure Agent machine that runs the mapping.
Click
Edit
.
In the System Configuration Details section, select
Data Integration Server
as the Service and
DTM
as the Type.
Edit the JVMOption system property and set the value to
-DHonorInfaDateFormat=true
.
Click
Save
.

When you set the JVM option system property for the DTM type to

-DHonorInfaDateFormat=false

for the Secure Agent and also set the optional property

OptimizeCastsInPDO=true

in the Google BigQuery connection and run a mapping to read the time data in string data type from an Amazon S3 or Google Cloud Storage source and write to date/time data type, the mapping fails with the following error:

The Secure Agent failed to run the full SQL ELT query due to the following error: [Invalid timestamp: '12/31/1972 00:00:00.000001']

To resolve this issue, set the JVM option

-DHonorInfaDateFormat=true

for the Secure Agent and also provide the source data with the format provided in the

DateTime Format String

advanced session property in the mapping task.

Perform the following steps to configure the JVM option in Administrator:

Select
Administrator
Runtime Environments
.
On the Runtime Environments page, select the Secure Agent machine that runs the mapping.
Click
Edit
.
In the System Configuration Details section, select
Data Integration Server
as the Service and
DTM
as the Type.
Edit the JVMOption system property and set the value to
-DHonorInfaDateFormat=true
.
Click
Save
.