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:
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.