Did you tried to create a Shared Cache file in the SSIS?
by shared I mean that a cache file which you can write on it multiple times through package execution.
When you use a Cache Transform in data flow task and write data stream on it, if you try to fill it again in another data flow you will get this error:
[Cache Transform ] Error: The component "Cache Transform" cannot write to the cache because component "Cache Transform" has already written to it.
Solution:
To solve this issue you need to first find the reason, As you may guess the reason is:
SSIS opens the Cache connection and don’t close it till package complete. This means that every another try to write data into cache file will fail, because connection manager is open through package .
So you can solve this issue with creating a Parent/Child structure. This means that create a child package to fill data into cache file. and then use Execute Package Task to run the child package.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.