MS Connector Attunity Teradata source export Unicode data error

Tools

MS Connector Attunity Teradata source export Unicode data error

Hi,

I am currenlty working on a project which is using SSIS to load data from Teradata to sql server. I found there is a SSIS component MS Connector Attunity which can be used for Teradata source and Destination with great performance of data loading. So I tried this tool in my SSIS package. The performance is better comparing with the ODBC. But after I did some testing with our DW data. There is a issue of Unicode data which I still don't find a solution. Can anyone help on this.

The detail of the issue:

1. Our Teradata (14.10) DW uses the UTF16 for the unicode char set.

2. I installed the MS Connector Attunity for Teradata and TPT and Teradata ODBC, the SSIS package can work well if the teradata source does not have a unicode column. But if one column uses Unicode CharSet, the dataflow in the SSIS pacakge will faile. The error message is:

[Teradata Source [31]] Error: TPT Export error encountered during Initiate phase. TPTAPI_INFRA: API306: Error: Conflicting data length for column(1) - Categ_GRP. Source column's data length is (16) Target column's data length is (24).

And this error is from the Teradata source side.

I researched a lot and it mainly because in my SSIS pacakge the driver is UTF8, but the teradata source is UTF16, UTF16 has 2 bytes for each char but UTF8 has 3 bytes. The Teradata source component get the metadata from the source server but it can't convert to UTF. That is the reason.

Does anyone else who has the same issue before like this?  Thanks a lot for your help.

1 REPLY
N/A

Re: MS Connector Attunity Teradata source export Unicode data error

I was battling with the same issue and the resolution for me was to use the TRANSLATE function to convert the problem column from unicode to latin. 

Using your error message this would be: 

TRANSLATE(Categ_GRP.Source USING UNICODE_TO_LATIN WITH ERROR) AS Categ_GRP

I also used nvarchar() columns (i.e. unicode data types) on my target SQL Server table. My next step will be to try using varchar() data types instead of my columns and see if the TRANSLATE alone is enough to fix the problem.