Pre 2012, expressions were limited to 4k. That's not a Variable's limit, you can assign stupid-long values to a Variable, it just can't be done with expressions. Use a Script Task to do the concatenation and assignment to your variable and see if that doesn't clear up #1
The thing you're likely to run into, and that I never found a satisfactory resolution, was the double firing of messages. I know, they say set propagate to false and all of that but I never seemed to get it to work as I wanted. In our world, a package would fail if an error raised so the OnTaskFailed event was more useful for only getting the signal once but your mileage my vary.
A better approach we used at a different job was to have a process sweep the sys.ssiserrorlog table every N minutes looking for failures. Since all packages logged to it, it was a cleaner and more reusable approach for us.
I've seen people make mention of always running packages from the context of a parent package to adhere to the DRY (don't repeat yourself) principle so the parent has the error notification and such built into it.
The final bit to be aware of, don't rely on SSIS's error handling exclusively. We had a deploy that went well, or so we thought. When the package fired though, it failed to validate as the metadata for a table didn't match what the data flow expected. A package failing to validate is like a syntax error and so the OnError/OnTaskFailed event's didn't even get a chance to fire and tell us that we were in an awful state. And we went a few days without noticing. Oopsie
Best Answer
SSIS Transaction Level
The default transaction level for all tasks and containers is
Supportedwhich means they will enlist in an transaction if available but they will not create one..A
Requiredsettings will start a transaction if one does not exist and enlist in an existing transaction.NotSupportedindicates that the Executable/Container will ignore any existing transactions which may result in deadlocks depending on your design.Your case
You have 3 Tasks and the desire is to have the third item behave in an atomic way. The answer is, partially, that you'll want to set the Transaction option to
Required. Partially because you can commit throughout your data load. In an OLE DB Destination, if you have a non-zero commit size then the committed data is fully committed. A rollback isn't going to pull that data back out.