Data Exchange supports SQL Server memory optimized tables as a transformation target but not as a transformation source.
Following are the limitations when memory optimized tables are used as a target:
Supports only the Insert operation.
Data Exchange commits the implicit transactions at the READCOMMITTED isolation level. Updating or deleting data in the memory optimized table in such a manner is not supported. Therefore, the SQL Server memory optimized tables only support Insert operation in Data Exchange. Using the DML command substitution, you can choose the target command to be Insert, in which case the transformation is supported.
In order to use the other operations such as Update, Delete, or Merge, you can use the following database setting:
ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;
This command changes the isolation level to SNAPSHOT when accessing the target memory optimized tables and therefore enables the memory optimized tables to support the other operations.
Row size is limited to 8060 bytes for SQL Server versions below SQL Server 2016.
For SQL Server memory optimized tables as the transformation target, the limit for row size is 8060 bytes. If the mapped source data size exceeds this limit, while running the transformation, the Administration Site displays an “Update target error” message, and based on the selected error handling policy, the transformation either continues or stops.
Resume transformation is not supported for non-durable mode.
SQL Server memory optimized tables can exist in the durable or the non-durable mode. If it is in the non-durable mode, when the database server restarts, the data in the table is lost. This can lead to inaccurate data in the target table when resuming a transformation.
For example, when a user resumes a transformation, if unknown state transactions are detected, the Admin site displays the transactions and asks the user to choose if it was committed or not. Now, if the target database server restarts, the data in the target table is lost and hence the user will not be able to check if a transaction was committed or not. This can lead to incorrect data being entered by the users, which in turn lead to duplicate or missing transactions in the target.
Primary key columns in SQL Server memory optimized tables cannot be updated. To update the primary key, delete the old row and then insert a new row with the updated primary key.