Can you show us what is happening in the Data Conversion task, please?

Also, include an example of the data in 'Val COArea Crcy'.

What is the OLEDB...

Here's the code again, formatted for readability:

BEGIN TRY
  BEGIN TRAN;

  IF (
    1 = 2
   )
  BEGIN
   ...

Note that having mega packages which do everything is generally regarded as a bad design practice. When things go wrong, errors become difficult to track down and solve and maintenance...