The error and the solution described in this post are for the following set-up
SAP Data Services - 14.2.x
Database - MS SQL Server
ABAP dataflow is a reliable method to extract large volume data using SAP Data Services. In a batch processing mode the ABAP dataflow option is convenient.
The error "Invalid character value for cast specification" occurs with MS SQL Server for two different scenarios.
Scenario 1 - Datatype conversion due to unsupported datatypes between external and internal datatypes.
Scenario 2 - Null values in SAP that should be handled different in SAP DS.
For Scenario 1 - Datatype issue
For this scenario I would like to use EKET table as example.
A typical example will be the case where the extraction of the ABAP dataflow works fine for about a substantial volume and then the job terminates with this error.
"
Invalid character value for cast specification" as shown in Fig 1 below
The reason for this error is data type mismatch. With large volumes being migrated, the data type needs to be matched as per the external and internal conversion to/from internal datatypes of SAP Data Services.
One of the datatypes that cause this error is numeric datatype from SAP. The external datatype numeric is translated to Varchar in SAP Data Services (DS). The different external data types to the corresponding DS types are listed in the technical manuals for the product. a screenshot of the same for version 14.2 is provided in Fig 2 below
In Fig 3 the different data types available in the target mapping within an ABAP dataflow is provided. The datatype numeric is not listed. As per the table above, numeric needs to be mapped to varchar. This datatype needs to be amended for such fields in the target mapping. Similarly check the other columns where such external data type to DS data type needs to be mapped.
Subsequently the target mapping of the ABAP Dataflow (ADF) output to a table into SQL Server table should be set to decimal(x,y) as numeric datatype is not available in the target schema mapping as shown in Fig 4 below.
From Fig 4 the mapping for the columns EBELP and ETENR are changed from Varchar to decimal(x,y). Note that the precision of the data type is the same as in case of the column in the SAP table with numeric data type.
Scenario 2 - Handling NULLs
In this case, for version of SAP DS that is 14.x choose "Convert SAP null to null" in the Designer options as shown in Fig 5.
Click on OK and choose "Yes" to overwrite the Job Server parameters.
This will resolve the issue in case of null values being extracted in the ABAP Dataflow.