question

VivekD-8637 avatar image
0 Votes"
VivekD-8637 asked Monalv-msft edited

SSIS: DTS_E_INDUCEDTRANSFORMFAILUREONERROR error after upgrading to SQL 2019

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.

73201-image.png


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
73836-image.png


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.



sql-server-integration-services
image.png (36.5 KiB)
image.png (1.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft edited

Hi @VivekD-8637 ,

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.



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The external source is a view in MySQL showing data type for the column as varchar(60) and the destination is table in SQL Server DB with data type varchar(180). This is supposed to work without any kind of transformation and was working fine prior to the upgrade too.

0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 commented

@VivekD-8637,

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.

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Just tried that and now I get the error below i.e. cannot convert between unicode and non-unicode string data types, which is the same error/issue I'm having with the ADO .Net connector.

What's puzzling me is this package is running perfectly fine currently in production (SQL Server 2017).

73838-image.png


0 Votes 0 ·
image.png (14.1 KiB)

You need to refresh metadata for the following:

  • ODBC Source, NVARCHAR should be used for the column in question.

  • OLE DB Destination.


0 Votes 0 ·
VivekD-8637 avatar image VivekD-8637 YitzhakKhabinsky-0887 ·

I actually re-created the data flow task from scratch.

Here's what the input/output properties window looks like for the ODBC (MySQL) source, specifically the "street" column.

73933-image.png

73840-image.png

and this is what the input/output properties window looks like for the Ole DB(SQL Server 2019) destination.

73914-image.png


I've normally never touched these settings but are you suggesting something here should be changed?

0 Votes 0 ·
image.png (44.2 KiB)
image.png (48.1 KiB)
image.png (49.9 KiB)
Show more comments