3

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.

3

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:

  • Thank you - are you saying that using a TVP allows you to iteratively insert data into tempdb? (so you therefore don't need to load all the data into a the parser's/client's memory) – Zach Smith Jun 20 '17 at 5:49
2

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

  • Just looking into this now, are there performance gains in this over staging? If that is the case, what would be the use case for staging in tables or TVPs? – Zach Smith Jun 20 '17 at 5:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.