.NET provider and SSRS

Connectivity

.NET provider and SSRS

Does anyone have a way to create cached reports using SQL Server Reporting Services using the .NET Data provider? I keep getting an error about the .NET provider not supporting IDbConnectionExtension.

R
8 REPLIES
Teradata Employee

Re: .NET provider and SSRS

IDbConnectionExtension interface is not part of the ADO.NET specification.
This interface is designed for and used by SQL Server Reporting Services.

What is a "Cached Report"? Do you mean a report that asks for User Id and Password?

Re: .NET provider and SSRS

SSRS can maintain cached report sets that keep the load off of the DB. In order to use them, you have to store credentials in SSRS encrypted storage. We use them for reports that the data is only updated periodically. In looking at the MS web site, their preferred method is to extend the data provider with this interface, second is to create a wrapper SSRS data provider. These credentials are submitted via the IDbConnectionExtension interface.

R
Teradata Employee

Re: .NET provider and SSRS

To enable the credentials tab and maybe the Cached Report:

1- Create Visual C# Class Library Project. Set the project name to YYYY.SSRS.DataExtension.
Replace YYYY with your company name.

2- Add References to:
Microsoft.ReportingServices.Interfaces
Microsoft.ReportingServices.Diagnostics
Microsoft.ReportingServices.DataExtensions
Teradata.Client.Provider

Note: Reporting Services assemblies are in IDE\PrivateAssemblies directory of MS Visual Studio 2005.

3- Cut and paste the following code; replace YYYY with your company name.

using System;
using System.Collections.Generic;
using System.Text;

using Microsoft.ReportingServices.DataExtensions;

using Teradata.Client.Provider;

namespace YYYY.SSRS.DataExtension
{

public class TdConnectionWrapper : ConnectionExtension
{

public TdConnectionWrapper() : base(new TdConnection())
{

}

protected override String AppendCredentials()
{

TdConnectionStringBuilder conStrBuilder =
new TdConnectionStringBuilder(base.m_connectionString);

if (base.m_integratedSecurity == true)
{
conStrBuilder.IntegratedSecurity = true;
}
else
{
conStrBuilder.PersistSecurityInfo = true;
conStrBuilder.Password = base.m_password;
conStrBuilder.UserId = base.m_userName;
}

return (conStrBuilder.ConnectionString);
}

public override String LocalizedName
{
get
{
return (@"Teradata");
}
}
}
}


4- Build the project

5- Copy YYYY.SSRS.DataExtensions.dll to Visual Studio PrivateAssemblies directory

6- Change RSReportDesigner.config to use YYYY.SSRS.DataExtensions.dll

<Data>

...
<Extension Name="Teradata" Type="YYYY.SSRS.DataExtension.TdConnectionWrapper,YYYY.SSRS.DataExtension"/>

<Designer>
...
<Extension Name="Teradata" Type="Microsoft.ReportingServices.QueryDesigners.GenericQueryDesigner,Microsoft.ReportingServices.QueryDesigners"/>

RE: "their preferred method is to extend the data provider with this interface"
I think Microsoft should add the interface to the ADO.NET specification (System.Data.Common) if they expect Provider writers (STeradata, Oracle, IBM, SYBASE and others) to support it. Note that the .NET Data Provider for SQL Server does not support this interface either. Rather SSRS uses a wrapper for .NET Data Provider for SQL Server.

Re: .NET provider and SSRS

I looked at the code and have one question. Will I have to put a reference to the IDbConnectionExtension interface so that SSRS knows that this DLL supports it?

R
Teradata Employee

Re: .NET provider and SSRS

No, the base class ConnectionExtension implements the IDbConnectionExtension interface.
Teradata Employee

Re: .NET provider and SSRS

Refer to Deploying a Data Processing Extension when you are ready to deploy your extension to the report server.

Re: .NET provider and SSRS

Thanks for your help. I will implement this tonight and see how it works.

R

Re: .NET provider and SSRS

That works like a charm. Thanks.

R