Where should I store the CAW file for a Cache Connection Manager when using the cache in several packages?

Question

I am developing an ETL process and I will be using the same lookup in several packages. Instead of creating a new cache for each package I would like to create the cache once and reference it for each package. I am planning on saving the cache to file so it can be shared among multiple packages but I am not sure where I should put that file. Also, what is the best way of having one location for the file being used in development and another location in production? I thought of using a parameter but it doesn't seem like that is possible.

Answer 1

I'm fairly new to cache but I LOVE it!!

Since you're using SSIS 2012, are you deploying in the project model? If so, you can create a new cache project connection (although it MIGHT work as a package connection too). Then you can intialize the cache connection in one of the first steps of the package. And then any child package can reference the cache data source. It's really slick..

Right-Click Connection Managers Choose "Cache" Name the new Cache Connection On the columns table, add the columns in the lookup Click ok

In the parent package, initialze the cache dataset: Create a new dataflow task Source: Can be anything. SQL Query Destination: Cache Transform

Voila!

Now any child package can use the cache as a data source.