The configuration options available on a SSIS load are available on the Source tab of the Load table's Properties. These options are:
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 dialog) from the source details of this table's columns. Note 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. Refer to Load Table Transformations for details. |
Exit Status when Missing Source Columns | Load exit status when source columns are missing. Only enabled when Allow Missing Source Columns is set to True. |
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. Tip 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 is executed, instead of generated SQL. For a linked database, specify a complete INSERT statement. For an ODBC source, specify only the SELECT statement. |
Allow Missing Source Columns | Oracle only: Allows the load to happen when one or more of the source columns do not exist. (Refer to handling missing source columns for details). |
SQL Server Integration Services (SSIS)
Fields | Description |
|---|---|
SSIS Source-Identifier Encapsulation | Characters that are used to enclose source column names. Options are (None), "", [], '', `` |
SSIS Source-Identifier Case Conversion | Case-sensitivity conversion applied to Source Object Identifiers (such as table, view, and column names) in RED-generated SSIS packages. If no conversion is applied, then the exact case of the identifier defined in the RED metadata is used in SSIS. |
SSIS Destination-Identifier Case Conversion | Case-sensitivity conversion applied to Destination Object Identifiers (such as table, view, and column names) in RED-generated SSIS packages. If no conversion is applied, then the exact case of the identifier defined in the RED metadata is used in SSIS. |
SSIS Source-AlwaysUseDefaultCodePage | Forces the use of the DefaultCodePage property value when describing character data. |
SSIS Destination AlwaysUseDefaultCodePage | SQL Server, Oracle and Greenplum - Forces the use of the DefaultCodePage property value when describing character data. |
SSIS Set Destination-Code Page | SQL Server, Oracle and Greenplum - Enables the SSIS Destination Code Page property. |
SSIS Row Count Log | During an SSIS Load, include Row Count logging. |
SQL Server only options | |
SSIS Acquire Table Lock | Specifies whether the destination table is locked during the load. This enables the SQL Server to reduce lock escalation overheads during loading and will promote minimal logging in SQL 2008+ when a Bulk Logged recovery model is used for the database. The default value of this property is true. |
SQL SSIS Commit Interval | Specifies the batch size that the OLE DB destination tries to commit during fast load operations. The default value of 2147483647 indicates that all data is committed in a single batch after all rows have been processed. If you provide a value for this property, the destination commits rows in batches that are the smaller of (a) the Maximum insert commit size, or (b) the remaining rows in the buffer that is currently being processed. |
SQL Set SSIS Batch Size | Specifies the number of rows in a batch. The default value of this property is empty, which indicates that no value has been assigned. |
SSIS Enable Fastload | Enables the SSIS Fastload property.
|
PDW only options | |
SSIS Load Mode | Select the SSIS loading mode from the list. The available options are Append, Fastappend, Reload and Upsert. When the Upsert option is selected, the user must specify a key to use for the Upsert. The Upsert Key should be a business key for the loaded data. Note
|
SSIS Upsert Key Columns | Columns selected to make up the Upsert Key. The Upsert Key should be a business key for the loaded data. This can be typed in as comma-separated list of column names or alternatively, users can use the column selection dialog by clicking the ellipsis button to the right of the SSIS Upsert Key Columns field, to select the desired columns. |
SSIS Rollback on Table Failure | In the event of a SSIS failure, a rollback is performed. |
SSIS Use Staging Database | If this option is set and a staging database is defined on the target connection, then the staging database is used instead. |
SQL Server SSIS load Source screen example:
PDW SSIS load Source screen example: