As of SQL Server 2019 we have support for Unicode with encoding of UTF-8. This supports using a simple VARCHAR data type, which is a game changer (this is basically the same we have in Oracle). Therefore, using SQL Server 2019 and above the procedure is simple and you just need to use the right connector. The target table can use UTF-8 using data type VARCHAR
Before SQL Server 2019 there was no native support to store data in UTF-8. The older servers use only UTF-16 using a special data type NVARCHAR. This make everything more challenging and this is the source of your issue. The SSIS read the data from Oracle and try to use VARCHAR in the target but the source is UNICODE and VARCHAR before 2019 does not support Unicode. Therefore, the implicit convert between the source Unicode to the target non-unicode fails.
The solution is to explicitly "inform" the service that your target is NVARCHAR and not VARCHAR, and this is done using explicit CONVERT.
Option #1: Add Data Conversion transformations to convert all the string columns from non-Unicode (DT_STR) to Unicode (DT_WSTR) strings (meaning add a data conversion block into your data flow diagram).
Option #2: Try this:
1). right click on source task and select "Show Advanced editor"
2). Go to "Input and Output Properties" tab and select the string output columns
3). Change the data type from "String[DT_STR]" into "Unicode String[DT_WSTR]".
Please inform us if this solve your needs
In such case it is definitely a deficiency of the driver that you are using.
In that case, do I need to change the settings or any other update to make?