Insert a datetime value with SSIS to an datetime column in a table on a IBM AS/400 library/system

Question

I have the following problem.

I want make a SSIS flow that inserts values from a table in a MS SQL server database to a table in a AS/400 library.

This works for all the values but the datetime2 value. I tried to insert a datetime2 value and a varchar value in the datetime2 format. Both ways did not work.

It gives the following error:

[TABLENAME [11566]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "IBMDA400 Command" Hresult: 0x80004005 Description: "CWBZZ5014 Value of parameter COLUMNNAME could not be converted to the host data type.". An OLE DB record is available. Source: "IBMDA400 Command" Hresult: 0x80004005 Description: "CWBZZ5014 Value of parameter COLUMNNAME could not be converted to the host data type.".

[TABLENAME [11566]] Error: There was an error with input column "COLUMNAME" (12245) on input "OLE DB Destination Input" (11579). The column status returned was: "The value could not be converted because of a potential loss of data.".

Does someone knows how to get this done?

Answer 1

I assume conversion in sql scripting could be better way of mapping destination column in IBM , try this.

CONVERT(TIMESTAMP, @DateTimeVariable ) or

use derived column expression and there by use data conversion.

I would like to try db_timestamp

Data Conversion