What is the best way to stage data in SSIS?

Question

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.

Answer 1

I think it is better to pass values as a Table Valued Parameter (TVP).

TVPs can efficiently pass large amounts of data.

This is efficient because the client API internally bulk inserts the rows into tempdb before executing the procedure. And you don't need to load all the data into memory on the client.

You can also take a look at this helpful article to learn more on TVP performance:

• Maximizing Performance with Table-Valued Parameters

Answer 2

Also check out SSIS Data Streaming, which allows you to consume the output of an SSIS package in TSQL without staging it.