Using Teradata .NET data provider as data source

Question

Recently I installed the .NET data provider for Teradata.  In a regular C# application I can add the namespace, "using Teradata.Client.Provider;" to connect to this data provider directly without going though ODBC or OLD DB so I know it works. 

However, when I open Reporting Services (new project -> Business Intelligence Projects) I do not see how to add the Teradata .NET provider.  I only see the same standard data types as before.  I have searched and I do not see how to add the Teradata .NET provider to the available "Type" list when you click “Add New Data Source” to your report.

Thanks!

Answer 1

Managed providers (such as Oracle's ODP, Teradata's .NET provider, IBM's DB2.NET, SAP .NET, MySQL's .NET) need to be registered in the RSReportServer.config and in the RSReportDesigner.config to have them show up as data source types.
 
Example for Oracle's ODP provider:
     <Extension Name="ODP" Type="Oracle.DataAccess.Client.OracleConnection,Oracle.DataAccess"/>

Explanation:
 * "Oracle.DataAccess" is the name of the assembly contains Oracle's ODP data provider
 * "Oracle.DataAccess.Client.OracleConnection" is the name of the class in the ODP data provider that implements System.Data.IDbConnection.
 
Please consult the documentation of Teradata's .NET provider to find out the class name implementing System.Data.IDbConnection. 


(Parameterized) Queries: 
Note: when registering a managed data provider through the config files, you can then only use the text-based query designer. You will be able to successfully execute queries and parameterized queries (but you need to check the third party data provider documentation to find out if the data provider supports unnamed or named parameters and the syntax to use for parameters).
 

Stored Procedures:
You can also invoke stored procedures through third party .NET providers - as long as they do not return datasets through cursors. If a database uses the Cursor-concept to return data, there will be no way around implementing a custom data extension that wraps the third party .NET data provider and does the Cursor parameter handling in a similar way as discussed in the MSDN article below. It  discusses ADO.NET and how to deal with Oracle REF cursors in stored procedures (scroll to the "Oracle REF CURSORs" section near the bottom of the article):
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp

 

Hope this helps,
Robert
 

Answer 2

Thank you Robert! I was able to add Teradata to the list, however it was not able to connect using the following:

- Connection: Teradata.Client.Provider.TdConnection
- Provider: Teradata.Client.Provider

According to your example above I assume that should work.
In my C# apps I am able to successfully add:

using Teradata.Client.Provider
TdConnection tdc = new TdConnection(strConnect)

I am using the same connection string. I am not sure why I can not get Reporting Services to connect.

Answer 3

What is the dll & assembly name of the Teradata provider? I suppose you are using the following config file entry: <Extension Name="Teradata" Type="Teradata.Client.Provider.TdConnection,Teradata.Client.Provider"/>

Is the dll/assembly name Teradata.Client.Provider.dll? Is the provider installed into the GAC?

-- Robert

Answer 4

Thanks again for the reply.

The assembly name is Teradata.Client.Provider.dll, and it is installed in the GAC.

In my .config files I am using:
<Extension Name="Teradata" Type="Teradata.Client.Provider.TdConnection,Teradata.Client.Provider"/>

From what I understand,  theoretically it should be working.  hmmm..

I don't suppose there is anything else I can try.

Answer 5

I was able to get it working by using the following:

In rsreportserver.config and RSReportDesigner.config add to the <data> tag
<Extension Name="Teradata" Type="Teradata.Client.Provider.TdConnection,Teradata.Client.Provider, Version=1.0.0.0, Culture=neutral, PublicKeyToken=76b417ee2e04956c"/>
*note* The Version, Cutlure, and PublicKeyToken can be found in C:\Windows\assembly

In RSReportDesigner.config add to the <Designer> tag
<Extension Name="Teradata" Type="Microsoft.ReportingServices.QueryDesigners.GenericQueryDesigner,Microsoft.ReportingServices.QueryDesigners"/>

Answer 6

Jason, so now that you got SSRS to work with Teradata, can you share your experience on how its working, performing, etc. I'm most interested in connectivity and performance between SSRS and Teradata, as well as if you had any experience or knowledge about SSIS and Teradata.

Thanks,

Len Z.

Answer 7

This thread has been very helpful. 

 

I've been working with all of the available data providers so far in the hopes that one of them will support named parameters from within SSRS. So far they all support positional parameters, but not named parameters.

 

Has anyone out there figured out if named parameters are even possible using the ADONET driver?  I have not found any reference in the Teradata online documentation to indicate that they are, but also have not found anything to the contrary either.

 

Also I would second Len's request on any observations about the performance of the different drivers.

 

Regards,

 

Clayton

Answer 8

can you please provide me .dll of teradata , i browsed to my machine and could not found it

---

Why does RPC error is thru when connecting Integraitons Services

Answer 9

This thread was started long ago :) since then we added some native support for teradata. This post from Robert's blog clarify's the subject and provides links to download locations of the .Net provider for Teradata.
 http://blogs.msdn.com/robertbruckner/archive/2008/12/26/using-teradata-net-data-provider.aspx

For the question about named parameter support, only unnamed (positional) parameters are supported.

Thanks
Boreki

---

http://www.boreki.eng.br