SSIS Multiple Table Inserts vs ALL Union

Question

I am new to SSIS and I am writing my first SSIS package.

In my package, there are several stages of conversion in the data stream, and for each stage the script component inserts records (some kind of user log) into the ADO.NET address.

I use several ADO.NET Destination components that point to the same table in the same database.

I wonder if this is the right approach or should I use Union ALL and insert all the records at once.

Any help would be appreciated.

Answer 1

In terms of performance, you would like to get a very small increase in performance if you combine the results together in SSIS and paste them into the database in one cartridge, because you will only create one connection to the instance / database and depending on when it’s dirty pages are written to disk, perhaps, but in no case is it guaranteed that the head may need less time to move around the spindle.

What I think is much more important is what is for user logging? If logging records how far you are in the package, how many records have been updated at a certain stage, etc., I would strongly advise against UNION in the package to insert them at the end for the following reason.

Imagine a scenario when a packet falls halfway, and you are tasked with first diagnosing the problem and, secondly, correcting some data that is damaged as a result of the failure. If you write all the journal entries at the end and the package is not completed, the journal entries will not be recorded, and your task will be much more difficult.

Instead, I would install ADO Connection Manager to keep the same connection. This way, the connection will remain open all the time, and you will not have the overhead of reconnecting.

Answer 2

I think Union All, and then insert all at once.

it will make your ssis understandable and get better performance. (as I knew, SSIS will use the server resource instead of the database resource, right?)