What is cache transform in SSIS? Or Explanation and use of cache transformation
Cache transform is cached dataset for lookup transformation. Let's assume we are loading user information but user data keeps country id and we have to replace country id by country name while country information are present in some different data source.
Approach 1: First populate all country data into an intermediate temporary table and while loading user's information perform join operation with temporary country table.
Approach 2: Instead of populating all country data into an intermediate temporary table keep it cache transform. And this will be most efficient approach.
Cache transform very simple example step by step:
Step 1: Create two data flow task. First data flow task to load all country data into cache by using Cache transform and second data flow task to load user and get country name using lookup transform and cached data.
|Cache transform example beginner|
Step 2: In the fast data flow task get the country data by using any source adapter. In the example, I am using ADO NET Source adapter. Connect with Cache transform:
Step 3: Now double click on Cache Transform. A Cache transformation editor will pop up. Choose Connection manager (left most side) and click on new connection it will pop up Cache connection manager editor.
|Cache connection manager SSIS|
Step 4: Open columns tab. Change to index position 1 for column ntCountryID. (Keep the increment value in index position of key columns of join query and keep zero for non-key columns). After that, click on OK.
Step 5: Click on mapping tab (Here we can rename destination column) and then click on.
|Cache transform mapping SSIS|
Step 6: Move back to control flow area and double click on data flow task "Load User data". Add ADO NET Source to fetch data from user table to get users information. Then add lookup transformation and connect with data source.
|Cache transfrom Lookup|
Step 7: Double click on Lookup transformation. It will pop up lookup transformation editor. Under general tab choose Cache mode as full cache and connection type as Cache connection manager.
|Cache transform Full Cache|
Step 8: Now click on connection tab and choose cache connection manager which we have created at step 3.
Step 9: In the column tab, connect the key columns from available input columns to available lookup columns. In this example our key column is ntCountryID. Now select the remaining columns from available lookup columns which we want to populate at the destination. Then click on OK.
Step 10: Now connect the lookup transformation to any destination. In this example it is ADO NET destination and chose the lookup match output. Then execute the package.
|Execute cache transform SSIS|