Retail Scheduler Job – Apply failed | The given value of type String from the data source cannot be converted to type datetime of the specified target column

Hi Guys, Hope you all are keeping up with RetailDAXing. In this blog, I will help you troubleshoot one of those many scheduler job errors which annoy everyone. Sometimes it gets very hard to troubleshoot and find the exact reason for the cause. This is my small efforts to help you with these error.

This blog is about the error you get in the Download session form under message column-

Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.

ProcessSourceRequestHeaderException: ProcessTargetRequestHeader failed due to an InvalidOperationException.
connectionString: Data Source=XXX\SQLEXPRESS;Initial Catalog=<ChannelDBName>;Integrated Security=True;Connect Timeout=60;Application Name=”Commerce Data Exchange Async Client” —> System.InvalidOperationException: The given value of type String from the data source cannot be converted to type datetime of the specified target column. —> System.FormatException: Failed to convert parameter value from a String to a DateTime. —> System.FormatException: String was not recognized as a valid DateTime.
at System.DateTime.Parse(String s, IFormatProvider provider)
at System.Convert.ToDateTime(String value, IFormatProvider provider)
at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)
— End of inner exception stack trace —
at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)
at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata, Boolean isNull, Boolean& isSqlType, Boolean& coercedToDataFeed)
— End of inner exception stack trace —
at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata, Boolean isNull, Boolean& isSqlType, Boolean& coercedToDataFeed)
at System.Data.SqlClient.SqlBulkCopy.ReadWriteColumnValueAsync(Int32 col)
at System.Data.SqlClient.SqlBulkCopy.CopyColumnsAsync(Int32 col, TaskCompletionSource`1 source)
at System.Data.SqlClient.SqlBulkCopy.CopyRowsAsync(Int32 rowsSoFar, Int32 totalRows, CancellationToken cts, TaskCompletionSource`1 source)
at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinued

(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsync(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync

(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource`1 source)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync

(CancellationToken cts, TaskCompletionSource`1 source)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync

(CancellationToken ctoken)
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)
at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.

SCSqlWriteRequestRunner.BulkCopyData(SqlConnection connection, SqlTransaction transaction)
at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.

SCSqlWriteRequestRunner.Run(SqlConnection connection, SqlTransaction transaction)
at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.

SCSqlTargetRequestHandler.ProcessWriteRequest(SqlConnection connection, SqlTransaction transaction)
at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.

SCSqlTargetRequestHandler.ProcessTargetRequestHeader

(ISCTargetRequestHeader targetRequestHeader)
— End of inner exception stack trace —
at Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.

SCSqlTargetRequestHandler.ProcessTargetRequestHeader(ISCTargetRequestHeader targetRequestHeader)
at Microsoft.Dynamics.Retail.SynchClient.Core.

DownloadAgent.ApplySessionFileToClientDatabase(SessionManager sessionMgr, String fileName)

This issue occurs due to 2 reasons –

Force locale setting

Go to Retail >> Setup >> Retail scheduler >> Channel integration >> Channel data group.

Remove the Force locale from the Data group and run the job again.

Time difference

If the POS store machine and Headquarter machine is on different time zone, then you will face this issue. The tolerance allowed between POS and HQ time zone difference is 4 min + or -.

Do share your experience on scheduler job errors so everyone can take a benefit.

Feel free to reach out for any clarifications. If you like my blog posts then comment and subscribe to the blogs.

Please follow me on Facebook | Google+ | Twitter | Skype (sumit0417)

Enjoy RetailDAXing.

Disclaimer: The information in the weblog is provided “AS IS”; with no warranties, and confers no rights. All blog entries and editorial comments are the opinions of the author.