Column Collation (Model Object)

Column Collation (ColumnCollation) is the collation that the generated format file declares for every column of a source file. For UTF-8 files, set it to DATABASE_DEFAULT or leave it empty, and set Code Page to 65001.

The property exists only in the Microsoft SQL Server Stage File generator.

Where to set it

Set Column Collation when you edit a model object of type Source. It is a text field. Every Stage that reads the Source uses the same value.

Values

• A SQL Server collation name, for example SQL_Latin1_General_CP1_CI_AS: the format file declares this collation for each column.
• DATABASE_DEFAULT or empty: the format file declares no collation (""), and the loaded text gets the collation of the database.

Default value

SQL_Latin1_General_CP1_CI_AS.

Effect on the generated format file

The generator writes one format file per Stage, named after the Stage, for example load_from_raw_ansi.fmt for the Stage load_from_raw_ansi, which reads the Source CUSTOMER. The collation is the last value of each column line:

1  SQLCHAR  0  0  "|"  1  "CUSTOMER_ID"  SQL_Latin1_General_CP1_CI_AS
2  SQLCHAR  0  0  "|"  2  "CUSTOMER"     SQL_Latin1_General_CP1_CI_AS

With DATABASE_DEFAULT the same lines end with "". The spacing is shortened here.

Example

The UTF-8 file CUSTOMER.csv contains the name Müller AG and the city Łódź. The Source has Code Page 65001, and the Stage database uses SQL_Latin1_General_CP1_CI_AS.

• Column Collation SQL_Latin1_General_CP1_CI_AS: the Stage stores Müller AG.
• Column Collation DATABASE_DEFAULT: the Stage stores Müller AG and Lódz.

Characters that the code page of the database collation cannot represent are replaced, as Ł by L and ź by z in this example.

Related articles

• Reference: Code Page (Model Object), Column Delimiter (Model Object)
• See also: Model a Stage File Solution, Microsoft SQL Server - Stage Files