Where should I store the CAW file for the cache connection manager when using the cache across multiple packages?
I am developing an ETL process and I will be using the same search across multiple 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 plan on saving the cache to a file so that it can be used across multiple packages, but I'm not sure where I should put this file. Also, what is the best way to have one location for a file used in development and a different location in production? I thought about using a parameter, but it doesn't seem like it's possible.
source to share
I'm new to cache, but I LOVE it !!
Since you are using SSIS 2012, are you deploying to the project model? If so, you can create a new connection to the cache project (although it MAY work like a package connection). Then you can set up a 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 smooth.
Connection managers right click Select "Cache" Name the new cache connection In the column table add columns in the search Click ok
In the parent package, initialize the cache dataset: Create a new dataflow task Source: Can be anything. SQL Query Purpose: Transform Cache
Voila!
Now any child package can use the cache as a data source.
source to share