SSIS – Compare Length of Two String Variables in Expression Task

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

That's either a problem with MySql or with the driver you're using. All SSIS knows is that the driver says 'I can't do that'.

The place to start is by manually inserting as record of that kind into the destination db and looking for a more meaningful error.