We are using SSIS for ETL purpose. There is a shortcoming in SSIS as it doesn't provide any in built component which will bulk upsert the destination. The alternative to it to use Staging tables which is not an option as we already discarded it from maintenance perspective. While exploring the market we found ZappySys's "SSIS Upsert Destination" component which might fulfill our requirements.
Having said that, there is a caveat that it uses temporary tables as Staging tables and do the operations like upsert. Since we would be dealing with millions of rows, it is something which is not normal in our environment to use tempdb for such workload and might not be recommended.
So do we need to consider anything before proceeding with it? Anyone using the above mentioned component who can share the experience?