Page History
This topic describes the details of the connection Properties as they apply to the Database type connections.
When coming to the Data Warehouse from an OLTP source system, the connection defines the path to get to those source tables. A connection object must be defined for each source system.
The fields presented in the Connection Properties screen below, depend on the connection and database types selected. See below for a description of all possible fields.
SQL Server: when running a SQL Server data warehouse it is possible to create database links (linked server) to other SQL Server instances and most of the other major databases, such as DB2, etc.
...
Source System
Options | Description |
---|---|
Provider Name | Name of the connection provider/driver used to connect to the database. |
Database Identifier or Database Name.Database Host/Server | PDW Database type connections only: Used for sqlcmd specification. |
Database Port | PDW Database type connections only: Used for sqlcmd specification. |
Database ID | Database identifier or database name. |
Database Link Name | Optional name of a Database Link that is used to access the database. If SQL Server and the database are on the same server as the data warehouse then no link needs to be defined and this field can be left blank. |
Provider Name | Name of the connection provider/driver used to connect to the database. |
Full Database Path Name | This field is used when you enter Microsoft.Jet.OLEDB.4.0 as the Provider Name, e.g. when the source database is from MS Access or Excel. Enter the full database path name. |
...
Advanced Connect Parameters
Options | Description |
---|---|
Session User ID | Database User that has access to SELECT from the source system tables to extract data. |
Extract User Password | The password of the data warehouse user. For SQL Server blank if a trusted login or the server login password. |
Administrator User ID | Database User ID to log in when using WhereScape SQL Admin via the 'Connect using SQL Admin (as SQL Admin User)' context menu item (optional). | Administrator User Password | Optional
Session User ID used for Advanced Connect. | |
Session Password | Session Password used for Advanced Connect. |
Connection String | Used when establishing a connection, accepts automatic replacements of RED Tokens |
. |
Other
Options | Description | Default Database for Browsing | Optional comma-delimited list of databases for the browser pane filter. Enter the database(s) you want the connection to browse by default on the right browser pane.|
---|---|---|---|
Default Schema for Browsing | Optional comma-delimited list of schemas for the browser pane filter. Enter the schema(s) you want the connection to browse by default on the right browser pane. | ||
New Table Default Load Type | The default Load Type for new Load tables is created using this connection as a source. Select the desired default load type from the list, e.g. Database Link Load, Script-based load, Integration Services Load or Externally Loaded.
| ||
New Table Default Load Script Connection | The default Script Connection to use when a Script-based load type is defined for a Load table object that is sourced from this connection. | ||
New Table Default Load Script Template | The default Script Template to use when a Script-based load type is defined for a Load table object that is sourced from this connection. | ||
SSIS Connection String (OLEDB) | Connection string to be used by Microsoft SQL Server Integration Services (SSIS) to connect to the data source using the OLE DB Connection Manager. | ||
Data Type Mapping Set | Mapping Set to use when converting from a source database data type to a destination database data type. Setting this field to (Default) makes RED automatically select the relevant mapping set, otherwise, you can choose one of the standard mapping sets from the drop-down list or create a new set. | ||
Default Transform Function Set | Function Set that is selected by default in the Transformation dialogs. |
Tip |
---|
Once the connection has been set up, you can right-click the connection in the middle pane or double-click the connection name from the left pane to view or edit the connection's Properties. |
...