|
<< Click to Display Table of Contents >> ETL > 14.0 u1 > Implementation Guide > Tutorial and implementation guide > Steps > Mapping > Advanced topics Arbitrary column types |
By default, the type of each column is inferred from the context. For simply queries, the type may be inferred from the source table, while for more complex it is either based on the type of the expression or the actual value type.
In some cases, it may be required to use a specific type, for example to enforce that a string-based column is written as CHAR(20) and not NVARCHAR(MAX). The same applies to numeric formats, which also may have different precisions (INT, SMALLINT, TINYINT etc.).
To define an arbitrary type for a column, use the property type, for example:
{
"id": 1,
"type": "map",
"name": "Example for arbitrary column types",
"source": "SourceTable",
"columns": {
"TypeFromParent": { "from": "ColumnA" },
"TypeFromExpression": { "from": "ColumnA", "transform": "Uppercase" },
"ArbitraryType": { "from": "ColumnA", "type": "nvarchar(100)" },
},
"target": "TargetTable"
}
This property is optional - when omitted, the type will be inferred automatically. Note that the type can be defined for any type of column: simple mapping, a fixed value, custom SQL etc.
Several types and their aliases are accepted. You can use MSSQL types or DbType enum values (https://docs.microsoft.com/en-us/dotnet/api/system.data.dbtype?view=net-5.0). Depending on the implementation, some types may be unavailable (for example XML or currency).
Examples of valid types (the list is not comprehensive, see the referenced link for a full list):
•int
•bigint
•smallint
•nvarchar
•varchar(max)
•char(20)
•datetime
•numeric(18,0)
•string
•bit