extending RS for Connection String only

I know there have been many questions regarding extending RS to report off of
different data sources (xml, ado etc) - we are successfully running our
reports off of SQL Server stored procedures and are using the web services
(not url) to render directly to PDF from a custom asp .net UI. We would like
to leave this as is, tying the RS Datasource to a SQL Server stored procedure
and leaving it at that. Is it possible to use only the connection interfaces
(IDbConnection) in conjunction with the web services to programmatically
switch between datasources? Or do you have to implement the whole nine yards
(IDbCommand, Parameter, Transaction, Reader etc.) and run reports off of a
custom dataset in order to switch the connection string?
Thanks,ok, I realize maybe that was a stupid question. Here is another one. If we
need to create a custom data extension in order to manipulate the connection
string, is it possible to create a 'custom' data extension that executes SQL
Stored Procedures using the .net SqlCommand for its 'custom data', seeing as
how we have the reports up and running using the stored procedures?
I am trying desperately to figure out the best approach to take. We need to
be able to switch to one of 60 + databases depending on the user requesting
the report. The custom code is in place, the reports done and working - what
would anyone suggest would be the best way to accomplish the last piece of
this puzzle?
"Myles" wrote:
> I know there have been many questions regarding extending RS to report off of
> different data sources (xml, ado etc) - we are successfully running our
> reports off of SQL Server stored procedures and are using the web services
> (not url) to render directly to PDF from a custom asp .net UI. We would like
> to leave this as is, tying the RS Datasource to a SQL Server stored procedure
> and leaving it at that. Is it possible to use only the connection interfaces
> (IDbConnection) in conjunction with the web services to programmatically
> switch between datasources? Or do you have to implement the whole nine yards
> (IDbCommand, Parameter, Transaction, Reader etc.) and run reports off of a
> custom dataset in order to switch the connection string?
> Thanks,

extended connection managers for data flow destinations?

It looks like you can only extended connection managers for data flow sources.

Is there anyway to develop a custom connection manager for the SQL destination in the data flow destinations?

I can’t use hardcoded connection strings.I can’t use configurations because they are not encrypted.I already have managed code that will give the corrected connection string.I already have a custom connection manager that I use from the data flow sources.I just need one for data flow destinations but I can’t see a way to extend into the OLE DB?

The OLEDB Destination is just like the OLEDB source and can use the same connection managers. However, you are using the SQL destination and that is a special destination and needs the specific connection manager that it supports. If you want to use your custom connection manager then switch to using the OLEDB destination instead and it should just work.

HTH,

Matt

|||

oledb destinations only support the special destinations as well. there is no way I can see to use a custom connection manger in any of the destinations.

except for the script component and in that you can do whatever you want.

|||

Actually they support anything they see as an OLEDB connection. I can create different types of OLEDB connections and the OLEDB destination will support them (e.g. excel, sql (sqlncli), sql (sqloledb), etc). It does indeed have to be an OLEDB type connection though.

Matt

|||

ok so I think you might have answered my question.

If I develop a custom connection mgr derived from connectionmanagerbase I can't use that custom connection mgr with any of the stock data flow destinations.

because only OLEDB connections can be used and there is no way to create a custom OLEDB connection.

so there is no way to do what I wanted to do.

except the script componet can do what I want but I have to override the correct portions....

|||

Well there are ways to create custom OLEDB connections but you would have to write your own OLEDB provider, which not many would want to do so I guess in your case a script would probably be the best alternative.

Matt

Expression-based Data Source + subscription = No-go?

Set up reports to use expression-based data sources and everything works fine as far as executing and viewing the reports. The ConnectionString expression includes the credentials, and the "No credentials required" option is selected.

Unfortunately, am getting this when trying to create a subscription for same report. WTF?

eportingServicesService!processing!3!12/20/2006-12:20:07:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'XXXData'., ;
Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'XXXData'. > System.InvalidOperationException: The ConnectionString property has not been initialized.
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.ReportingServices.DataExtensions.SqlConnectionWrapper.Open()
at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.ReportRuntimeDataSourceNode.OpenConnection(DataSource dataSourceObj, ReportProcessingContext pc)
End of inner exception stack trace

You probably use something like “User!UserID” in data source expression. It’s forbidden for subscriptions (http://msdn2.microsoft.com/en-gb/library/ms156307.aspx) as there is no user at subscription execution time. Also, you probably created subscription first and changed data source definition after – otherwise you would get error during subscription creation.

|||

No, I'm not doing anything like that. In fact, it kind of seems like a MSFT bug to me?

Here is the expression for it:

=Assembly.Namespace.ClassName.StaticMethod("ConnectionStrings", Parameters!VariableName.Value, "KeyGroupInConfigFile")

Basically, it uses an assembly to pull a full connection string out (including the credentials) of a config file based on a key (Parameters!VariableName.Value).

|||Moreover, the subscription is saving the proper value for Parameters!VariableName.Value.|||

Most likely the custom assembly does not have enough permissions to access the config file and fails with a FileIOException or a code access security exception.

Can you check if the account configured to run the RS Windows Service has permissions in the file system to read from your configuration file?

-- Robert

|||

Will check that and report back...

|||

So I double-checked and the Windows service is currently running under the Local System account. So, it would seem all the rights would be there for it? Also, the config file in question is actually the RS Server web.config.

Would there be some sort of extra .Net configuration to do (keeping in mind that I pretty much made the trust on the custom assembly wide-open)?

|||Shameless reply to get back to the top. Anyone at MSFT able to help?|||Anyone?|||It most likely not an issue with user permissions. Are you sure your assembly has full trust?|||

Yes, as mentioned in previous post, the assembly does have full trust. Mind you, it works fine when running it, just the subscriptions are failing.

It kind of seems like the sequence of events when getting a subscription report ready has getting the datasources ready as the 1st step. If the datasource is based on expression, and that expression uses a saved parameter value, it bombs. Can you confirm or refute that? Thanks

|||

OK, problem solved! And I almost feel ashamed to admit what it was.

Essentially, as I mentioned in one of my first posts, the custom assembly looks to some values stored in the web.config. That's fine on the UI, but a subscription uses the WINDOWS service, and as such, my specialized web.config code wasn't working. A slight tweak to the assembly got everything on the right track.

Thanks to those who offered advice.

|||

Hi Bingo,
What was the tweak?

Thanks

Expression-based Data Source + subscription = No-go?

Set up reports to use expression-based data sources and everything works fine as far as executing and viewing the reports. The ConnectionString expression includes the credentials, and the "No credentials required" option is selected.

Unfortunately, am getting this when trying to create a subscription for same report. WTF?

eportingServicesService!processing!3!12/20/2006-12:20:07:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'XXXData'., ;
Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'XXXData'. > System.InvalidOperationException: The ConnectionString property has not been initialized.
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.ReportingServices.DataExtensions.SqlConnectionWrapper.Open()
at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.ReportRuntimeDataSourceNode.OpenConnection(DataSource dataSourceObj, ReportProcessingContext pc)
End of inner exception stack trace

You probably use something like “User!UserID” in data source expression. It’s forbidden for subscriptions (http://msdn2.microsoft.com/en-gb/library/ms156307.aspx) as there is no user at subscription execution time. Also, you probably created subscription first and changed data source definition after – otherwise you would get error during subscription creation.

|||

No, I'm not doing anything like that. In fact, it kind of seems like a MSFT bug to me?

Here is the expression for it:

=Assembly.Namespace.ClassName.StaticMethod("ConnectionStrings", Parameters!VariableName.Value, "KeyGroupInConfigFile")

Basically, it uses an assembly to pull a full connection string out (including the credentials) of a config file based on a key (Parameters!VariableName.Value).

|||Moreover, the subscription is saving the proper value for Parameters!VariableName.Value.|||

Most likely the custom assembly does not have enough permissions to access the config file and fails with a FileIOException or a code access security exception.

Can you check if the account configured to run the RS Windows Service has permissions in the file system to read from your configuration file?

-- Robert

|||

Will check that and report back...

|||

So I double-checked and the Windows service is currently running under the Local System account. So, it would seem all the rights would be there for it? Also, the config file in question is actually the RS Server web.config.

Would there be some sort of extra .Net configuration to do (keeping in mind that I pretty much made the trust on the custom assembly wide-open)?

|||Shameless reply to get back to the top. Anyone at MSFT able to help?|||Anyone?|||It most likely not an issue with user permissions. Are you sure your assembly has full trust?|||

Yes, as mentioned in previous post, the assembly does have full trust. Mind you, it works fine when running it, just the subscriptions are failing.

It kind of seems like the sequence of events when getting a subscription report ready has getting the datasources ready as the 1st step. If the datasource is based on expression, and that expression uses a saved parameter value, it bombs. Can you confirm or refute that? Thanks

|||

OK, problem solved! And I almost feel ashamed to admit what it was.

Essentially, as I mentioned in one of my first posts, the custom assembly looks to some values stored in the web.config. That's fine on the UI, but a subscription uses the WINDOWS service, and as such, my specialized web.config code wasn't working. A slight tweak to the assembly got everything on the right track.

Thanks to those who offered advice.

|||

Hi Bingo,
What was the tweak?

Thanks