2

How does SSDT handle batching of Data Flow tasks?

I am parsing a CSV that is about 1GB into SQL Server using SSDT.

In the Data Flow there is a Flat File source, that goes to an OLE DB destination (a staging table). Then an SP is executed using a SQL Task.

The CSV being parsed contains a summary table and a child table with a foreign key reference to the summary table. As such the CSV contains duplicated IDs of the summary table (so there is one line per child row). If a single summary ID were to be split across two batches, then I would lose data - the SP does something like "delete from child table where ID in staging, then re-insert from staging into child table". Previously we had to do this because the vendor wasn't exporting a unique ID for the child data. They are now, so I can use a merge statement.

But. I would still like to know if Data Flow tasks are batched, and if so, how?

Improve this question
1
  • 1
    Do you mean SSIS? SSDT is just an IDE. A Data Flow has a default buffer of 10k. References here.
    – Marian
    Feb 14, 2018 at 10:07

1 Answer 1

Reset to default
0

As mentioned in the comment under the question, an answer to this question is included this post: http://blogs.lobsterpot.com.au/2011/02/17/the-ssis-tuning-tip-that-everyone-misses/

That is, that batching is done by default (10k rows per batch).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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