HOW TO USE CACHE TRANSFORM

            

           HOW EFFECTIVELY WE CAN USE CACHE TRANSFORM IN SSIS

Scenario:

• While using look up transformation, ur pointing to look up table which is available in database for checking the source data table.
• If u r checking for single source data table there is no problem because it only one time hit the database for getting the data from lookup table.
• But in real time scenario u may use more than one source table to look up on one single lookup table, in this situation database hits will increase(that many times it will hit the database for lookup table) and performance will automatically decreases.

To overcome this situation I used cache transformation in SSIS Data flow task.

Explanation:

Step 1:

           Drag and drop data flow task in control flow, open data flow and add oledb source and make all the database connection then choose the look up table from that database.

Step 2:

           Now add cache transformation to data flow and map oledb to cache and configure the cache connection manager like below.

        Double click on cache transformation and check the use file cache then select the .caw (cache file) file (for getting this .caw file open notepad and save as .caw extension.).

      After that go to column tab in cache connection manager to select one index column.

Step 3:

           Now add another data flow task in control flow and then open data flow and add oledb source for getting the source table. Add look up transformation to data flow and then map source table to look up then open look up and in cache mode select full cache then in connection type use cache connection manager.

Step 4:

           Now go to connection tab in look up and select that cache connection manager (already created connection to .caw file).

     

     Then go to columns tab and map on which column u r going to look up.

Then click on OK.

Now u can redirect the rows (matched and not matched) to desired destinations.

Finally:

             U can use same cache file so many times u want to look up with source tables so that it will hit the database only single time. 

In this above screen shot u can find the three different sources but having the one look up file so this reducing the database hits ,and increases the performance. 

THANKS FOR VISITING BLOG