Memory Optimized Property
As of version 126.96.36.1992, the Dimension Merge Slowly Changing Dimensions component includes a new property called Memory Optimized. This new property was introduced due to the retention of a large number of records to remain in memory in the original behavior. This caused memory usage of the package to grow significantly over the package execution time. By requiring the inputs to be sorted, this new model is able to process and remove rows from memory much earlier thus reducing the overall memory footprint and speeding up execution. While there is no setting within the user-interface, users can configure this option by right-clicking the Dimension Merge component and selecting Properties.
Please note the following additional information about the Memory Optimized property:
• When set to false, the component operates as it did in the past. Upgraded packages will have this property set to 'False' by default.
• New packages will have this property set to 'True' and will have to meet certain restrictions (listed below) otherwise users will encounter errors.
• If the inputs are not marked as sorted, the component will use the original behavior, even if Memory Optimized is set to true.
• If the property is set to true, all input rows must be sorted by the Business Key using a binary sort order. Example:
Note the ORDER BY and COLLATE Latin1_general_bin clauses on this product dimension. This should be used when sorting within the source query and setting the Is Sorted/Sort Key properties in the Advanced Editor. When using a file as input then users will need to add the SSIS sort component between the source and Dimension Merge component.
• Because this is a significant processing change, it is recommended users test their packages with both settings before deployment to ensure performance is optimized based on their dataset and configuration.