I am exploring to load large datasets into destination tables using SSIS. I came across "Partition Switching" as one of methods to load data faster into destination tables.
But since the staging table has to be of the almost same configuration as destination table partition in order for partition switching to work, what is the real benefit here?
My concern is loading of the data after the cleanup/transformations. If staging table is exactly same as destination partition, loading time should be same for both. Or is it that loading data into a huge partitioned clustered indexed table will take more time versus loading into exactly same empty un-partitioned clustered indexed table?
We still have to fill up the staging table (which is almost same as destination), it's going to take the same time as to load data directly in destination partition, right? Why not load directly into destination then?
What is real benefit in terms of time to load the data here?