SSIS: DTS_E_INDUCEDTRANSFORMFAILUREONERROR error after upgrading to SQL 2019

Question

We have a SSIS package that extracts data from a MySQL DB view and loads it into a SQL Server table. This has been working fine for a couple of years now on SQL Server 2017. After we've upgraded to SQL Server 2019 though, it has started to give DTS_E_INDUCEDTRANSFORMFAILUREONERROR errors.

Actual error messages are in the below screenshot.

I've tried to debug and simplify the problem statement and finally figured the following 2 scenarios.

If I setup the ODBC Source step with the query below then it doesn't work.
select customer_id , customer_name from bi.customer;

If I set it up as the one below, it works.
select customer_id, CONVERT(customer_name, char(60)) as customer_name from bi.customer;

This if of course just a sample query so I don't want to go and change all packages like this.

Would like to know if anyone has any idea why this is happening and if there is a more standard solution to ensure we don't have to do all these changes to all packages.

UPDATE 3/3:
I was able to isolate this down to one row and column and it looks like something to do with special characters.
The value in question is as shown below

I basically tried to insert 1000 rows at a time and it initially worked but started to fail within a certain range. I further narrowed down the rows loaded and was able to identify a specific row where the package was failing (there may be more such rows/columns but this was the first instance) and when i try to insert just that row it fails.
Specifically, when I include the street column shown above it fails and it loads fine if I do not include that column.

Answer 1

Hi @Vivek Devalkar ,

1.Please change the data type of the column in the external source.

2.Or we can use Data Conversion Transformation to change the data type of the column.

Best regards,
Mona

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

@Vivek Devalkar ,

Due to your latest discovery, I would suggest to change SQL Server DB column from data type varchar(180) to NVARCHAR(180) to accomodate accented characters.