Using the SSIS visual designer, my goal is to load data from a CSV into a table of database X. To do this I've opted to 'stage' the data from the CSV and then select from that staged resource into the live tables (A single CSV will get split into may tables).
I can see two methods of achieving this 'staging' environment. Which of these would be considered best practice? (or what alternatives are recommended)?
As far as I know, either I can insert from a CSV into a TVP (Table Valued Parameter) and select from there (which I think is an in-memory resource), or I can insert into a static table, select from there, and then delete from the static table.
To me it seems that the static table would be slower but better in that it would allow for much larger CSV source files and not place a toll on system memory. Particularly if using a cloud box, the memory of a system might be limited. But then again it's unlikely I would ever get a CSV larger than a few hundred MB, so perhaps that doesn't matter.