SSIS Variable values can only be modified in the OnPreExecute and the OnPostExecute phase of pipeline execution. During execution, they are static.
You can verify this behavior by adding a column to the dataflow with a Derived Column Task and use add your variable @[User::lastName] after the Script Task. You'll see value never changes between the first and ...
Welcome aboard the good ship SSIS. I'll be your captain this evening... Not everyone's first SSIS package is going to be as interesting as yours so some of the things I'm going to cover are techniques I only worry about when things are in the 20% of the 80/20 rule.
SSIS package timings
When you run a package in Visual Studio/SSDT, it runs under the context ...
Data volume is one of the last criteria for choosing the ingestion pipeline implementation. You choose the tool for what it can do that your current tool cannot, and then you test it to see if can handle the volume (spoiler alert: it can; the database will be the bottleneck in 99.9% of the cases).
First, you seem to be missing a few zeros in the numbers you mentioned before you should start seeing problems (IMO)
Second, I've only seen Kafka as part of a data loading solution for ingesting data from multiple IoT devices.
In these instances, Kafka was used to solve the IoT problem.
ACID Compliant databases have problems ingesting a bunch of single row ...