SSIS lookup transformation cache working

0
votes

I have a scenario, wherein a SSIS (2008) package has a Data flow transformation (DFT) running inside a ForEach container.

The DFT gets the source data from a SQL server table and loads a cache file data into lookup transformation (Full cache mechanism) using a cache manager to look up for potential matches and the respective match\no-match results are dumped into a sql server table.

Questions 1. For the next iteration, will it reload the cache for look-up transformation for the subsequent look up task or will it use the same cache result loaded during previous iteration?

  1. If not, Is it best to use cache look-up mechanism for a DFT which runs in iteration?

  2. What could be the best possible solution apart from implementing the DFT logic entirely into SQL Server?

Any pointers would be of great help.

1
sql-servercachingssis
you're looping the source. How is your source query linked with loop result ? That point might help in suggesting alternative - Prabhat G
Source query changes for each loop, while look-up reference data remains the same for each.For the look-up transformation, I am using Cache connection manager as a connection type. - Arpan Mohokar

1 Answers

0
votes

For the next iteration, will it reload the cache for look-up transformation for the subsequent look up task or will it use the same cache result loaded during previous iteration?

-->It will be reloaded.

If not, Is it best to use cache look-up mechanism for a DFT which runs in iteration?

--> Good question. Better option if possible is to have that cache populated outside the DFT.

What could be the best possible solution apart from implementing the DFT logic entirely into SQL Server?

-->The basic idea of using Cache Manager is the ability to reuse it across multiple DFT's if needed. If it gets to be used within the DFT then it defeats that purpose. You may get partial performance benefit in doing this but it would not add much value of using Lookup itself.