Each data-store-specific SQL script contains the following types of information:
The file names used by Data Express to execute data-store-specific operations are:
For example, with the ODBC Extension, if a given table is accessed via the MYSOURCE DSN and the target data store for that table is MYTARGET, the configuration file names would be named:
Controlling configuration at the data store level allows data-store-specific SQL operations to be performed during before or after Data Express processing.
The following pre-processing configuration settings can be specified:
Specifies two characters to be used as an opening delimiter and a closing delimiter when the data store type requires that the schema name, table name, and column name identifiers needed to be delimited. For example, if working with Microsoft SQL Server data stores, the value would need to be set to DX:IdentifierDelimiter:[]
Specifies to maintain the identity values from the source database in the target database when the data store type requires that this functionality be enabled. Enabling this parameter causes a SET IDENTITY_INSERT statement to be executed, which will allow values to be inserted into the identity column of a given table. For example, when working with Microsoft SQL Server, DX:IdentityInsertOn:SET IDENTITY_INSERT ~ ON results in SET IDENTITY_INSERT MYSCHEMA.MYTABLE ON
By default, if the Always Drop Table parameter is set to N within dxeconfig.cfg and the table being processed already exists within the specified schema, the table contents will always be emptied prior to inserting processed values. (Data Express accomplishes this by executing the DELETE FROM statement, which removes rows from your table one at a time.) You can override this default by choosing a different option that allows your data store to perform more optimally such as the option TRUNCATE TABLE, which results in the execution of the TRUNCATE TABLE statement being executed. For example, DX:EmptyTableCommand:TRUNCATE TABLE results in TRUNCATE TABLE MYSCHEMA.MYTABLE
The following post-processing configuration setting can be specified:
Specifies the default behavior of using the automatically generated value for the identity column in the target database, which may cause result in differing identity values for the source and target databases. Disabling this parameter causes a SET IDENTITY_INSERT statement to be executed. For example, when working with Microsoft SQL Server, DX:IdentityInsertOn:SET IDENTITY_INSERT ~ OFF results in SET IDENTITY_INSERT MYSCHEMA.MYTABLE OFF
For your convenience, some sample scripts containing some of the options described in Configuration Options are provided in the config directory as templates in order to assist you in building your specific configuration: