Change request: Parameter mapping

In some places in the SSIS enviroment, you can use parameters in queries, for examples in the query part of the OLE DB Command component.

You do this by specifying placeholders using question marks. The question marks result in parameters on the Column Mappings tab of the Advanced Editor named Param_0, Param_1, etc. When adding question marks to the query later on, new parameters are created named Param_<x>, where ‘x’ is the next higher index number, no matter where the question mark appears in the query. If, for example, your initial query contains two parameters and later on you add a third one that appears as the first parameter in the query, it results in a new Param_3 on the Column Mappings tab.

This is all a bit confusing and not very wanted from a maintenance point of view. I know this might be a limitation caused by using OLE DB connections; we tried to do something with ADO.NET connections, but until now, not very succesful. Therefore we think this is something to put on a wish list for a next SSIS version. It would be great to have the opportunity to give parameters a meaningful name, in stead of the question marks in the queries and the generated Param_<x> names in other places.
Hans Geurtsen
MoreInfo BV

I couldn't agree more. I recently had to do the same thing for a pipeline with >50 columns in it. Debugging it when it didn't work was near impossible.

You should raise this request at http://lab.msdn.microsoft.com/productfeedback/default.aspx (and let us know because I will vote for it) and also on the "SSIS Requests" thread at the top of this forum.

-Jamie

Change Report Datasource

I have a c# assembly that uses the reporting services webservice to render reports.

Every user accessing this component needs to render this report passing different datasource to it in order to browse data in different databases (es.: test databse server, production database server)

Is it possible to create a linked report and change the connection only to it?

Other suggestions?

Thanks

Hi,

you can change the connection string property depending on the user (role or something you want to differentiate with). I don′t know if that is actually a solution for your problem but this is worth a try. using an expression could be something like

="Data Source=" & IIF(SomeExpression,"TestServer","ProductionServer") (...Rest of the connection string here...)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Yes, it works great with reporting services 2005, but i forgot to tell you that i have to do the same with reporting services 2000...that's the problem. Is it possible under 2000 to do the same thing? If "No", are there other ways to programmatically call a report with dinamyc conection string.

The only solution i've found is as follows:

==========

rs.render --> obtain the byte stream from REPORT_1

rs.CreateReport --> create REPORT_2 using the stream of REPORT_1

rs.GetReportDataSources(REPORT_2) --> to obtain datasource of REPORT_2

Modify the property ".connectString" of this DataSourceDefinition

rs.render to render the newly created report

finally delete the new report.

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

In this solution, copying the report slows down execution time i suppose...... Another solution might be to directly modify datasourcedefinition of REPORT_1 but if two or more users try to call the same report with different connectionstrings i think there might be problems.

Any suggestions?

|||This is something I would like to do. Where exactly would I enter that expression? In VS Report Designer, when I create a new Data Source, the "fx" button is grayed out. Is that where I should be entering this expression? Is there a reason why the builder is disabled?

Thanks,|||

With Reporting Services 2005, open the report in the solution and switch from design to code view (xml structure) and modify connectstring node as follows:

<ConnectString>="data source=" &amp; Parameters!ServerName.Value &amp; ";initial catalog=Northwind;"</ConnectString>

You also have to create the parameter "ServerName".

If you want, change also the Catalog Name using another parameter...

With reporting services 2000 you cannot do the same...try reading my code example reading abow...that works fine for me