How to increase cache memory size in SSIS

Introduction:

           In this article, I will explain cache modes and in which cases we can increase cache memory size.

Description:

            The most important setting of the Lookup Transform is the Cache Mode- It can greatly impact your dataflow performance and affects overall package design.

The Default Cache mode in Lookup is FullCache and Connection type is OLE DB Connection manager.

• In Full cache mode, the reference dataset is loaded into cache before the Lookup transformation runs. This approach uses the most memory, and adds additional startup time. By using this mode to reduce the number of queries sent to the server.

• In No cache mode, the lookup transform doesn’t maintain cache memory. In every time its hits database and get the data. This cache mode is mainly used the reference table is changing frequently.

• In Partial cache mode, the rows with matching entries in the reference dataset and optionally, the rows without matching entries in the dataset are stored in cache. When the memory size of the cache is exceeded, it automatically removes the least frequently used rows from the cache memory.

                In Full cache mode and No cache mode, there is no chance to increase the cache memory size. Only in Partial cache mode, you can increase the size of cache by following below steps.

Steps:

• Select Partial Cache mode in Lookup Transformation.

• Right click on Lookup transformation and select “Show Advanced Editor...” option.

• In Advanced Editor select Component properties. There are actually two separate values – one for 32 bit execution and one for 64 bit. The default value is 25 (it means 25 MB).

• The maximum size of cache memory in Partial mode is 3072 (it means 3 GB).

Note: If you try to increase the value more than 3 GB. Its shows the error as follows

Let me know what you think about this article.