Why does the Cache Transform in SSIS have a file cache option? When would I use this, especially when compared with using the default option which is to store the results in memory? One downside of the default option of storing the results in memory is that the cache is cleared immediately once the package is completed and that memory cache cannot be shared among other packages.

However, the File connection option will allow the results to be reused among multiple packages. But hold up, that’s not quite as good as you might think, each package still must read from the file and then subsequently load that file into memory!

So why are we discussing this if it’s essentially doing the same thing as the default option? Good point, and well received, did you read the title?

Imagine now that you have a rather large table that you need to access many times during your nightly ETL, this is very common in data warehousing situations. You can now lessen the effect of a poor performing network by using the file cache option in the cache transform. You can bring the data across the network only once, load it into a file, and in the future you can reference that file on your local machine and avoid  going across the network. As you can imagine this can be quite advantageous!

Set up is quite simple as well, see below:

1. Bring the cache transform into your data flow and connect it to your source.
2. Open the cache transform for editing.
3. Select NEW for cache connection manager.
4. 
5. Select “Use file Cache” and then browse to the location where the file will be stored.
6. Next click the Column tab at the top and specify at least one index column.
7. 
8. Click OK, map the columns, Click OK to close out the transform.

Now that the initial set up is complete I will demonstrate how to reference this file:

1. In the next data flow I set up an OLE DB source and connection that output to a Lookup transform.
2. Inside the lookup transform select “Cache Connection Manager” on the general screen.
3. 
4. On the connection screen select your cache connection manager from the drop down list.
5. 
6. And that’s it!, set up the rest of  your lookup transform like you normally would.

Thanks for looking!