Online Documentation for Data Pump for SQL Server

Configuration file format


The configuration (template) file used by Data Pump for SQL Server is divided into several sections, each corresponding to a particular group of settings specified at different steps of the GUI application wizard.

 

[#General#]

This section stores general information about the utility:

 

Parameter

Description

Product

internal product name

Version

major version

 

 

[Source]

This section stores the connection properties used to access the data source. The connection string is built according to parameters specified in the Data Link Properties dialog.

 

Parameter

Description

ConnectionString

connection string for the data source

 

 

[#Comment#]

This section stores the template file comment as specified optionally in the Save template options dialog:

 

Parameter

Description

Line<N>

comment text

where N stands for the comment line identifier

 

Example:

Line0=Data Pump for SQL Server

Line1=Template file #1

Line2=Pumping tables from MS Access to SQL Server

 

 

[Options]

This section stores data conversion and script execution options. The parameters correspond to the values specified at Step 4 and Step 8 of the Wizard application.

Parameter

Description

CommitAfter

number of records after which the COMMIT statement is inserted

DisplayErrorMessages

0 = error messages are hidden in the import log

1 = error messages are displayed in the import log

DetailErrorMessages

0 = simple error messages in the import log

1 = detailed error messages in the import log

ShowTableProgress

0 = simple data import progress in the import log

1 = detailed data import progress in the import log

ConvertNames

0 = object names are not converted

1 = object names are converted to conform to SQL92 naming rules

NamesFormat

nfAsIs = object names are left without changes

nfLowerCase = object names are converted to lower case

nfUpperCase = object names are converted to upper case

ConvertDefaultValues

0 = default values of the source database are not converted

1 = default values of the source database are converted to default values for the destination database

EnableScriptComments

0 = script comments are disabled

1 = script comments are included into the body of the script

DropIfExistDbStatement

the parameter is not used by Data Pump for SQL Server

StopScriptOnError

0 = script execution is not stopped on errors

1 = script execution is stopped if an error occurs

ShowExecutedStatements

0 = the Script execution information area does not display any information upon script execution at Step 6

1 = records for successfully executed statements are listed in the Script execution information area at Step 6

AlwaysSkipSuccExecuted

0 = upon subsequent script execution at Step 6 you are prompted to choose whether successfully executed statements should be skipped or not

1 = successfully executed statements are skipped upon subsequent script execution at Step 6

ClearTablesBeforeImport

0 = tables in the SQL Server database are not cleared before data import at Step 8

1 = tables in the SQL Server database are cleared before data import at Step 8

TrimStringSpaces

0 = source data strings

1 = unused space at the end of source data strings is cut off

QuoteNamesInImportSelQuery

0 = quoting source object identifiers is disabled

1 = quoting source object identifiers is enabled

QuoteTemplate

qtCustom = custom quoting characters (QuoteCharLeft, QuoteCharRight)

qtDefault = the default quotes for the data source in use

qtDoubleQuote = "..."

qtSingleQuote = '...'

qtApostrophe = `...`

qtSquareBracket = [...]

qtRoundBracket = (...)

qtBrace = {...}

qtAngleBracket = <...>

QuoteCharLeft

specifies the left quoting character (if QuoteTemplate = qtCustom)

QuoteCharRight

specifies the right quoting character (if QuoteTemplate = qtCustom)

SchemasMapping

smAsIs = the tables are placed into the default schema in the target database

smExisting = the tables are placed into an existing schema (SchemasMappingExist)

smNew = a new schema is created (SchemasMappingNew) and the tables are placed into this schema

SchemasMappingExist

name of the existing schema in the SQL Server database to pump the source tables into (if SchemasMapping = smExisting)

SchemasMappingNew

name of the new schema in the SQL Server database to pump the source tables into (if SchemasMapping = smNew)

GenerateDropTableStmt

0 = no DROP statements are generated

1 = the DROP statements are added for tables in the script

 

 

[Target]

This section stores connection parameters for the target SQL Server database. The parameters correspond to the values entered at Step 1 of the Wizard application and are obligatory.

Parameter

Description

Host

host/instance where the target database resides

Login

SQL Server login (if NTAuth = 0)

Password

password to identify SQL Server login (encrypted)

RemoteIndex

0 = local connection

1 = remote connection

DataBase

target database name

DatabaseActions

cdtCreate = a new database is created for pumping data

cdtSelectExisting = an existing database is selected for pumping data

cdtRecreate = an existing database is dropped and then recreated

NTAuth

0 = SQL Server authentication

1 = Windows authentication

 

 

[{TBL}<table_name>]

Sections of this type contain table properties (those that were changed at Step 5 of the Wizard application) and data import options specified at Step 7.

Note: Sections of this type are only available in dynamic templates (for details see Save template options).

Parameter

Description

Name

table name

NameSpace

SQL Server schema to save the table into

Excluded

1 = the table is excluded from data import at Step 7

WhereClause

text of the WHERE condition for pumping data

 

 

[{FLD}<field_name>]

Sections of this type contain field properties (those that were changed at Step 5 of the Wizard application).

Note: Sections of this type are only available in dynamic templates (for details see Save template options).

Parameter

Description

Name

field name

Type

data type applied to the field

Size

size of the field

Scale

scale for the field values

Precision

precision for the field values

Default

value that inserted records get by default

NotNULL

0 = nullable

1 = NOT NULL

AutoIncrement

0 = non-identity field

1 = identity field

NotForRepl

0 = for replication

1 = not for replication

Seed

value used for the first row that is loaded into the table

Increment

value added to the identity value of the previous row that is loaded

RowGuid

0 = a regular column

1 = a row GUID column

 

 

[{IND}<index_name>]

Sections of this type contain index properties (those that were changed at Step 5 of the Wizard application).

Note: Sections of this type are only available in dynamic templates (for details see Save template options).

Parameter

Description

Name

index name

Unique

0 = non-unique index

1 = unique index

Clustered

0 = non-clustered index

1 = clustered index

PadIndex

0 = PAD_INDEX is set to OFF

1 = PAD_INDEX is set to ON

FillFactor

percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or rebuild

IgnoreDuplicate

0 = IGNORE_DUP_KEY is set to OFF

1 = IGNORE_DUP_KEY is set to ON

DropExisting

0 = existing index is not dropped

1 = existing index is dropped

Statics

0 = out-of-date index statistics are automatically recomputed

1 = out-of-date index statistics are not automatically recomputed

 

 

[{FK}<key_name>]

Sections of this type contain key properties (those that were changed at Step 5 of the Wizard application).

Note: Sections of this type are only available in dynamic templates (for details see Save template options).

Parameter

Description

Name

key name

DeleteAction

'on delete' rule (for foreign keys):

0 = NO ACTION

1 = CASCADE

2 = SET NULL

3 = SET DEFAULT

UpdateAction

'on update' rule (for foreign keys):

0 = NO ACTION

1 = CASCADE

2 = SET NULL

3 = SET DEFAULT

 

 

[TypeMapping]

This section stores source-target type mapping options. The parameters correspond to the values specified in the Type mapping dialog.

Note: This section is only available in dynamic templates (for details see Save template options).

 

 

[ExcludedFromStructure]

This section stores the list of source objects excluded from structure conversion at Step 3 of the Wizard application.

Note: This section is only available in dynamic templates (for details see Save template options).

 

Parameter

Description

{NSP}<schema_name>

excluded schema (if available)

{TBL}<table_name>

excluded table

{IND}<index_name>

excluded index

{FK}<key_name>

excluded key

 

 

[SelectedTables]

This section stores the list of source and target tables, as specified at Step 3 and Step 5 of the Wizard application.

Note: This section is only available in fixed templates (for details see Save template options).

 

Parameter

Description

TableCount

number of tables selected for pumping

SrcTableNS<N>

source schema name (if available)

SrcTableNM<N>

source table name

TrgTableNS0<N>

target schema name

TrgTableNM<N>

target table name

where N stands for the table identifier

 

Example:

[SelectedTables]

TableCount=2

SrcTableNS0=dbo

SrcTableNM0=EMPLOYEE

TrgTableNM0=EMPLOYEE

SrcTableNS1=dbo

SrcTableNM1=DEPARTMENT

TrgTableNM1=DEPARTMENT

 

 

[Table<N>]

Sections of this type contain the list of source and target fields of the table (N stands for the table identifier in [SelectedTables]), as specified at Step 3 and Step 5 of the Wizard application.

Note: This section is only available in fixed templates (for details see Save template options).

 

Parameter

Description

SrcField<N>

source field name

TrgField<N>

target field name

FieldCount

number of table fields selected for pumping

where N stands for the field identifier

 

Example:

[Table1]

SrcField0=DepartmentID

TrgField0=DEPARTMENTID

SrcField1=Name

TrgField1=NAME

SrcField2=GroupName

TrgField2=GROUPNAME

SrcField3=ModifiedDate

TrgField3=MODIFIEDDATE

FieldCount=4

 

 

[FixedInfo]

This section stores the result script as presented at Step 6 of the Wizard application.

Note: This section is only available in fixed templates (for details see Save template options).

 

Parameter

Description

Line<N>

script text

where N stands for the script line identifier