Welcome to Microsoft Q&A platform and thanks for posting your question here.
The issue you are facing is due to the fact that the DB Collation is set to "SQL_Latin1_General_CP1_CI_AS", which does not support Chinese characters. To resolve this issue, you need to change the collation of the database to one that supports Chinese characters, such as "Chinese_PRC_CI_AS" or "Chinese_Taiwan_Stroke_CI_AS".
As you mentioned that there are no options for UTF-8/UTF-16 for Excel, you can still save the Excel file as a CSV file and specify the encoding as UTF-8. To do this, follow these steps:
- Open the Excel file and select "Save As".
- Choose "CSV (Comma delimited)" as the file type and click "Save".
- In the "Save As" dialog box, click on the "Tools" dropdown and select "Web Options".
- In the "Web Options" dialog box, select the "Encoding" tab and choose "Unicode (UTF-8)" as the encoding.
- Click "OK" to close the dialog box and save the file.
Once you have saved the Excel file as a CSV file with UTF-8 encoding, you can use Azure Data Factory to load the data into SQL Server tables. Make sure to specify the correct encoding when configuring the data source in Azure Data Factory.
Reference
https://learn.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver15
https://support.microsoft.com/en-us/office/import-or-export-text-txt-or-csv-files-5250ac4c-663c-47ce-937b-339e391393ba
I hope this helps! Let me know if you have any further questions or concerns.