Microsoft Connector for Teradata for SQL Server 2019 is released
Published Nov 25 2019 12:33 AM 15.6K Views
Microsoft

Microsoft Connector for Teradata is the replacement of Attunity Teradata Connector starting from SQL Server 2019 and now it is released for preview. Microsoft Connector for Teradata provides compatibility with Attunity Teradata Connector in functionality and performance. Packages designed with Attunity Teradata Connector can run with Microsoft Connector for Teradata without any changes.

 

Installation

Prerequisite

Teradata ODBC driver and Teradata Parallel Transporter supported versions are installed from Teradata tools and utilities download site. Supported Teradata PT versions are: 

Teradata Parallel Transporter (Teradata PT) 16.10

Teradata Parallel Transporter (Teradata PT) 16.20

Go to document page for more details.

 

To install the connector for Teradata database, download and run the installer from the latest version of Microsoft connector for Teradata. Then follow the directions in the installation wizard.

After you install the connector, you must restart the SQL Server Integration Service to be sure that the Teradata source and destination works correctly.

 

Design and execute SSIS packages

Microsoft Connector for Teradata provides similar user experience with Attunity Teradata Connector. User can design new packages based on previous experience, using SSDT for VS 2017 since 15.9.0 or SSIS Projects for VS 2019, with targeting SQL server 2019.

Teradata source and destination are under Common category.

teradata-toolbox.png

Teradata connection manager is displayed as "TERADATA".

Teradata-CM.png

 

Existing SSIS packages which have been designed with Attunity Teradata Connector will be automatically upgraded to use Microsoft Connector for Teradata. The icons will be changed as well.

 

To design SSIS package targeting SQL Server 2017 and below, you will need to have Microsoft Connector for Teradata and install Microsoft Connector for Teradata by Attunity with corresponding version from below link:

 

26 Comments
Copper Contributor

Hello, I'm looking for the extension that installs the "OData Source" component, like in your screenshots. I'm using Visual Studio 2019, and I've already installed SSIS extension. Thank you.

Microsoft

@ArmandoContestabile "OData Source" is default component. Are you in the "Data Flow" tab in the design canvas? 

Copper Contributor

Getting this error when trying to copy data from "Teradata Source" to "OLE DB Destination" (MSSQL Server). Have adjusted  "JobMaxRowSize" and tried to change the "DiscardLargeRow" to both true and false. ANy suggestions? 

 

TITLE: Package Validation Error
------------------------------

Package Validation Error

------------------------------
ADDITIONAL INFORMATION:

Error at Data Flow Task [Teradata Source [16]]: The "Teradata Source" is missing the required property "JobMaxRowSize". The object is required to have the specified custom property.

Error at Data Flow Task [SSIS.Pipeline]: "Teradata Source" failed validation and returned validation status "VS_ISCORRUPT".

Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

------------------------------
BUTTONS:

OK
------------------------------

 

===================================

Error at Data Flow Task [Teradata Source [16]]: The "Teradata Source" is missing the required property "JobMaxRowSize". The object is required to have the specified custom property.

Error at Data Flow Task [SSIS.Pipeline]: "Teradata Source" failed validation and returned validation status "VS_ISCORRUPT".

Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

------------------------------
Program Location:

at Microsoft.DataTransformationServices.Project.DataTransformationsPackageDebugger.ValidateAndRunDebugger(Int32 flags, IOutputWindow outputWindow, DataTransformationsProjectConfigurationOptions options)
at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchDtsPackage(Int32 launchOptions, ProjectItem startupProjItem, DataTransformationsProjectConfigurationOptions options)
at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchActivePackage(Int32 launchOptions)
at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchDtsPackage(Int32 launchOptions, DataTransformationsProjectConfigurationOptions options)
at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.Launch(Int32 launchOptions, DataTransformationsProjectConfigurationOptions options)

Microsoft

@Kahlejj, we have identified an issue occurs when you design a package containing Teradata components in SSDT and set the TargetServerVersion lower than SQL2019. In that scenario, if you try to execute the package in SSDT, you will get the valiation error about missing properties like what you described. 

Is that your case?

We are working on the fix and it will be ready soon.

Temporary workaround for it: Instead of directly execute the package, close and reopen the package in the SSDT and then execute it.

Copper Contributor

@Junxiao_Ma - Is this connector tested? For some reason this doesn't work for us. Whenever I drag and drop the Teradata source, I get the following error. Can you please help with this

bharathsql86_0-1592852466771.png

 

Microsoft

Hi @bharathsql86 - seems it indicates that the bits are registered but are somewhat located in the wrong location. Please try to uninstall and then reinstalled the Teradata Connector. Please also make sure you have installed Teradata tools and utilities. 

Copper Contributor

Hi, I am getting error while setting up the connection. It says -  There was an error trying to establish an Teradata connection with the database server.

When I check connection string property on properties - it says "Teradata ODBC Driver is not installed." 

 

randomguy2020_3-1594705768182.png

 

My setup is as below:

 

randomguy2020_4-1594706035331.png

 

 

Can you please help me with this?

 

Thanks!

Microsoft

@randomguy2020, we found there's an issue for supporting TTU17.00. Please uninstall the TTU17.00 and just use the TTU16.20 instead for now. We will have an update to support the TTU17.00. Thanks.

Copper Contributor

Hi, I have visual studio 2019, TTU 16.20, and Microsoft Connector 1.0.

 

I was able to use the Microsoft connector to pull data out of the Teradata and write to another database successfully. 

 

I can find the TD table I want to write to, in the Teradata Destination.

 

However, I'm having issues now attempting to write to Teradata due to the ErrorTableName property in the Teradata Destination.   If I leave it blank, it attempts to create the error file in my user database, where I don't have any space allocated.  If I add it without quotes, like databasename.tablename it tries to prefix the database name with SSIS_.  If I add it using quotes like "DATABASENAME"."TABLENAME" it doesn't like the period between the database and table.  If I just use the database between double quotes it tries to add LOG to the end of it or at least I'm assuming it isn't jumping to another error. 

 

Is it actually trying to add LOG to the end of the value in ErrorTableName and how can I identify the database and table name to use for the error table when using the Teradata Destination to get it to work? 

 

Thanks!

Copper Contributor

I have another problem with this.

I have to transfere a lot of data from TERADATA to SQL SERVER 2019 to use columnstore index. My TERADATA server is overload.

 

SQL Server 2019 in Windows Cluster. 

Microsoft SQL Server 2019 (RTM-CU5) (KB4552255) - 15.0.4043.16 (X64)  Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: )

------------------------------------------------------------

TTU version 16.20.00.00

------------------------------------------------------------

SQL Server integration Services Teradata Connector version 15.0.2000.118 x86 and x64

------------------------------------------------------------

Visual studio 2019  Microsoft Visual Studio Community 2019 Version 16.6.5

ProjectServicesPackage Visual Studio Extension Detailed Info

SQL Server Data Tools 16.0.62006.03190
Microsoft SQL Server Data Tools

SQL Server Integration Services 15.0.2000.123
Microsoft SQL Server Integration Services Designer
Version 15.0.2000.123

 

AND

I can not transfer data from teradata to SQL SERVER 2019.

I recebe this error 

Error: 0xC000C13A at Data Flow Task, Teradata Source [25]: An error occurred when converting string to target codepage. Column name is [COLUM_NAME].

 

The data is transfer to sql server but the execution finish with ERROR.

Warning: 0x80019002 at Data Flow Task: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (10413) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Task failed: Data Flow Task

Microsoft

Hi, @simee5

It is a limitation now that the error table can only be created in the user database. It does not recognize the [schema].[tablename] format. We will provide a fix for it.

Copper Contributor

I have another issue now.  We used to be able to pull from views using the old Attunity connectors, but now it seems that a Teradata Source will only list tables from Teradata and not allow me to select a view.  Is there a way to pull data from Teradata using the new Microsoft connector for Teradata and views, since those are how we get access to the data in the underlying tables?

Microsoft

@simee5 Can you use below work-around?

Teradata Source Editor, view cannot be displayed when Data access mode is "Table Name – TPT Export". As work-around, use Advanced Editor of Teradata Source.

https://docs.microsoft.com/en-us/sql/integration-services/data-flow/teradata-connector?view=sql-serv...

Copper Contributor

Hello,

 

We are having the same issue as user (@simee5) regarding the Teradata error tables.

 

You mentioned a fix was coming for this issue - has that been released? If so, where can we update our drivers/tools to bring in this new change?

 

If not, is there an ETA on when the fix will be made available?

 

Thanks in advance!

Jordan

 

Copper Contributor

I am having the same issue as @Jordan_Haymond and @simee5  with the error tables.  Have you made any progress on this?

 

Is there any way to run the old Attunity drivers on SQL Server 2019 enterprise?  I have a version running on an old 2012 Sql server using the Attunity drivers but we are retiring that box soon and I am going to loose a bunch of ETL jobs if we cant get the error table fixed. 

 

Thank you.

Copper Contributor

Hello. I do data flow from teradata to ole db destination. When i run it in Visual Studio 2019 on client machine everything works ok. When i run ssis package via job in SSMS it loads numeric data with  leading minus sign like -123456 instead 123456. Drivers innstalled on client and server machine are the same.

Copper Contributor

Hello, I have problem when I select table from Teradata and click Preview or Mapping, error message popup :

 

Error at Data Flow Task [Teradata Destination [341]]: SQLSTATE: 07009, Message: [Teradata][ODBC] (10670) Invalid descriptor index, descriptor record does not exist, or descriptor record was not properly initialized.;

 

It is happen on tables with many columns. When I try on simple table (two columns), it work.

Copper Contributor

Component adds a prefix of SSIS_ to table name or database name depending on how the component is filled in ( CREATE TABLE SSIS_table_name...). Is there any workaround for this or is this going to be fixed?

Copper Contributor

Hello,

 

My Teradata TTU is on 17.10 and Visual studio version 17 and SQL server is 2014. We downloaded the Teradata connection Manager to connect to Teradata 17.10 and getting an error while trying to make a connection. 

 

"There was an error trying to establish an Teradata connection with the database server". How do we fix this?

 

Any responses is greatly appreciated. Thanks

 

Copper Contributor

I'm having the problem mentioned in the thread above where I get an error when I try to create the "Teradata Source" or "Teradata Destination" in a Data Flow...

 

Any chance any progress has been made on this issue?

The error:

 

===================================

The component could not be added to the Data Flow task.
Could not initialize the component. There is a potential problem in the ProvideComponentProperties method. (Microsoft Visual Studio)

===================================

Error at Data Flow Task [SSIS.Pipeline]: The module containing ", clsid {A21403DF-47CD-4066-A4AD-035C53DAE8E9}" cannot be located, even though it is registered.

Error at Data Flow Task [ [1]]: The component is missing, not registered, not upgradeable, or missing required interfaces. The contact information for this component is "".

 

===================================

Exception from HRESULT: 0xC0048021 (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------
Program Location:

at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ProvideComponentProperties()
at Microsoft.DataTransformationServices.Design.PipelineTaskDesigner.AddNewComponent(String clsid, Boolean throwOnError, Boolean select)

Microsoft

@eric_lord could you open support ticket for the issue? Thank you

Copper Contributor

Hi Team,
While  running Data flow task ( from OLEDB to Teradata destination)  I got an CLI Error 224(No text Message available)
Kindly someone help me to resolve this issue.

Thanks in advance

Nandish732_0-1709533456823.png

 




 

Copper Contributor

Hello Nandish732

I do not remember if this exact error i got but i know that i couldn't load to teradata into tables which had a column with "int" type. When i changed it to decimal it worked then. Just a hint. Try it in your case.

Regards.

Copper Contributor

Thank you for Your response @Pilawski ,
I will try and let you know.

Copper Contributor

Hi @Pilawski 

In  my table  there is no int or decimal columns.
Please anyone let us know  what  is fix for this issue?

 

Copper Contributor

Hi team,
Can any help me to solve this issue 
while using Teradata source connection In SSIS

[teradata source [2]] error: ' ' ('0a'x) is not a valid teradata sql token.

Thanks in advance

Version history
Last update:
‎Aug 03 2020 04:20 PM
Updated by: