Description

As per the ETL guide, 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.). 

This property is optional, and 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. 

Example

If a step includes a column that returns a NULL value either as a constant value or through SQL and the "Determine column type from context" option is enabled, then SQLite could consider the column type as being binary instead of text, potentially causing the end result of that step of subsequent steps to use a binary value which will look as though it has been encoded to the naked eye. 

Other column data values could be considered in a similar manner by SQLite, and this situation can be seen when viewing the column types of the result table or related troubleshooting tables.  For instance, the binary data type will be displayed as being blob when viewing a MariaDB database. 

Resolution

To rectify these types of situations one can simply disable the "Determine column type from context" option and set an expected column type, such as nvarchar(max).