From the error message the package appears to be failing on the OLE DB Source so I am wondering, is it even reaching the Data Conversion you said you added? How about the attempt to insert into the VARCHAR(MAX) column you mentioned?

Try changing the OLE DB Source component such that it processes the incoming data as a DT_WSTR. The second part of the error message says "or one or more characters had no match in the target code page".