Creating Report Datasets for Other Data Sources
Reporting Services supports accessing data sources through any Microsoft .NET Framework managed data provider, including OLE DB or ODBC. After you create a data source definition in your report, you specify a query in the query language of the data source to create a dataset. For more information about creating your own data provider to support custom data sources, see Implementing a Data Processing Extension.
Specifying Connection String Properties for a Data Provider
To set connection string properties specific to a data provider, from the General page of the Data Source Properties dialog box, click the Edit button to open the Connection Properties dialog box. For an OLE DB data source type, you can select the data provider for the data source and set extended data source properties through the Data Link Properties dialog box. For an ODBC data source type, you can set the connection string. For more information and example connection strings, see Connecting to a Data Source (Reporting Services).
Query Designer Support for OLE DB and ODBC Data Sources
Reporting Services provides a query designer to help you specify a query for an OLE DB or ODBC data source. By default, the text-based query designer opens in one of three modes: Text, TableDirect, or StoredProcedure. You can type a query, select all the columns from a database table, or select a stored procedure. For more information about how to use this query designer, see Text-based Query Designer User Interface.
Some Reporting Services features depend on implementations provided by the data provider. For example, some data providers do not support parameterized queries or do not support named parameters. For more information, see Using Query Parameters with Specific Data Sources (Reporting Services).
Query Designer Support for Other Data Sources
The query designer for each data provider is registered in the RSReportDesigner.config file. When you create a dataset based on a data source, Report Designer opens the dataset in the default view for the data provider. When new data processing extensions are installed, they either use the text-based query designer or provide a customized query designer for the data source.
For more information, see Query Designers and Data Processing Extensions (Reporting Services).
Mapping Data Types from the Data Provider for the Report
When you create a dataset, the data types of the fields are mapped to a subset of common language runtime (CLR) data types from the .NET Framework. Data types that cannot be clearly mapped are returned as strings. For more information about working with field data types, see Working with Fields in a Report Dataset. When you create a parameter, the data type must be a supported report definition data type. For more information about mapping data types from the data provider to a report parameter, see Working with Data Types in Expressions (Reporting Services).
Special Characters in a Password
If you configure your ODBC or OLE DB data source to prompt for a password or to include the password in the connection string, and a user enters the password with special characters such as punctuation marks, some underlying data source drivers cannot validate the special characters. When you process the report, the message "Not a valid password" may indicate this problem. If changing the password is impractical, you can work with your database administrator to store the appropriate credentials on the report server as part of a system ODBC data source name (DSN). For more information, see "OdbcConnection.ConnectionString" in the .NET Framework SDK documentation.
Note
It is recommended that you do not add login information such as passwords to the connection string. Report Designer provides a separate tab on the Data Source dialog box that you can use to enter credentials. These credentials are stored securely on the client computer running Report Designer.