The fields on the Source tab for Native ODBC loads are described below.
SQL Server Source Screen:
Fields | Description |
|---|---|
Load Type | Method of loading data into the table. The available options are dependent on the Source Connection. Defaults to the 'Default Load Type' of the Source Connection. Can be specified via the Properties screen. |
Source Connection | The connection that identifies the source database. Can be specified via the Properties screen. |
General
Fields | Description |
|---|---|
Select Distinct Values | Include the DISTINCT keyword in the SQL SELECT statement. |
Source Schema | Schema within the source database where the source table resides. |
Derive Source Tables(s) and Source Columns | Derive the Source Table(s) and Source Column(s) properties (of this screen) from the source details of this table's columns. The existing property values will be overwritten. |
Source Table(s) | Name of the table or tables that the data is sourced from. |
Override Source Column/Transformations | Ignore the source and transformation details of this table's columns and instead use the override details specified below. |
Where and Group By Clauses | Optional SQL SELECT WHERE-clause and/or GROUP BY-clause. Parameter names can be specified using $Pparameter_name$ (with leading $P and trailing $), which are replaced at run-time by the parameter's value. This is where you can build a statement to handle change data. Parameter values can be in-line replaced and included in the 'Where' clause. Prefix the parameter name with a '$P' and add a trailing '$'. For example, if we have a parameter called SALES_LOCATION_CODE we could construct a statement, such as WHERE location_code = '$PSALES_LOCATION_CODE$' AND region_code = 'NY'. When this statement is executed, the value of the parameter will replace the parameter name. For example, if the parameter was set to 'New York' then the statement would execute as WHERE location_code = 'New York' AND region_code = 'NY'. |
Override Load SQL | Optional SQL statement to load data into the table, which overrides all other properties. The specified SQL will be executed instead of the generated SQL. For a linked database specify a complete INSERT statement. For an ODBC source specify only the SELECT statement. |
Native ODBC Load
Fields | Description |
|---|---|
Native ODBC Load Routine | File Loader utility/mechanism to use to load the generated extract file. |
Field Delimiter | Character that separates the fields within each record of the generated extract file. The default value is a character (pipe). This should be changed if pipes are possible in the source data. |
Unicode Extract File | Data will be loaded via a Unicode format file. The default is unchecked and this field is not available FOR DB2. |
BULK INSERT Maximum Errors | SQL Server Only - Maximum number of errors accepted before a load failure is triggered. The default for this option is 0. |
BULK INSERT Additional Options | SQL Server Only - Optional comma-delimited list of options to control the behavior of the SQL Server BULK INSERT command. For example, the following options will respectively lock the table; set the batch size to 100,000 rows; and fail the load when the first error occurs: TABLOCK, BATCHSIZE=100000. |
Populate Load Parameters | Populate any load-related WhereScape RED parameters, which may be used for validation purposes. |