Issue with Microsoft Connector v1.0 by Attunity with Teradata
Recently I was trying out the SSIS connector for Teradata by Attunity in one of my sample SSIS packages. While the installation and the registration process of the components went fine I faced an issue when I tried to run a really simple SSIS Package pulling up data from a Teradata table “AccessLogV” and pushing it to a flat file on the local file system.
I set up the SSIS Package with a Teradata source and a Flat File Destination task. I selected the column “Logdate” from the table AccessLogV and wrote it to a Flat File. I could preview the data with success, however when I try to run the package from BIDS, I get the following error:
[Teradata Source [1]] Error: TPT Export error encountered during Initiate phase. **** 03:27:50 Output Schema does not match data from SELECT statement.
This error seems to come no matter whatever Destination task I use (Flat File, OLEDB, Sql Server and so on). I even tried setting the “ExtendedStringColumnsAllocation” to TRUE with no effect.
So, the error is not related to the destination type I am using. I believe the error comes from the TPT Export API, indicating that the output schema of the export operator doesn’t match the select statement specified. This is most likely related to the string columns I have in the table. I turned on tracing for the Teradata Source Component but unfortunately the trace file generated did not have anything much relevant, all I could find there of interest is:
EXPORT_OPERATOR: aborting due to the following error:
Output Schema does not match data from SELECT statement.
I worked with Attunity Support further regarding this and after some initial diagnosis we suspected that the problem we are facing is because of the Date/Time column in the table. The connector reads date/Time columns as string values and it seems that for some reason the TPT API is failing to read them as CHAR(8). As a work-around we decided to configure the source component to use SQL command data access mode and add a convert to the query which will convert the time column to string as follows:
select LogDate(char(8)) from DBC.AccessLog
Teradata Support forum has a discussion thread regarding this as well:
https://forums.teradata.com/forum/tools/teradata-pt-12-0-error-output-schema-does-not-match
Unfortunately, this didn’t fix the issue for me as well and this fix of explicit casting Date/Time values as string seems to be inconsistent and work against specific versions of Teradata Client Utilities (In My case I was using TPT 13.0). So finally, we agreed upon that fact that it may be considered as an issue with v1.0 of the connector. However, the GOOD News now is that we have come up with a new maintenance version of the connector, v1.1 which has the fix for this error. The new version of the connector can be downloaded from:
https://www.microsoft.com/downloads/details.aspx?displaylang=en&familyid=6732934c-2eea-4a7f-85a8-8ba102e6b631
Matt has a very nice blog post regarding the various new features implemented in the v1.1 of the connector here:
https://blogs.msdn.com/mattm/archive/2010/03/25/version-1-1-of-the-connectors-for-oracle-and-teradata-by-attunity-now-available.aspx
Author : Debarchan(MSFT), SQL Developer Engineer, Microsoft
Reviewed by : Jason(MSFT), SQL Escalation Services , Microsoft