Convert Access' Memo to a VARCHAR string

The problem: the Memo data type in Access is a Unicode text stream (i.e. no fixed width) and the VARCHAR type is a non Unicode text string (fixed width). So, if you're transferring data from a Memo column on an Access file to a database column of the VARCHAR(n) type, you'll have to use a Data Conversion transformation to convert the Memo to String (DT_STR, which maps to VARCHAR) and when you do, Integration Services will throw the following error

Conversion from DT_NTEXT to DT_STR is not supported

This means that you cannot directly convert a Unicode stream to a non Unicode string.
What you can do in this situation is create a Data Conversion that converts the Memo column to DT_TEXT (non Unicode stream) and after that use another Data Conversion to convert that same column from DT_TEXT to DT_STR. Keep in mind that truncation may occur if the conversion to DT_STR doesn't reserve enough width on the column to fit the whole stream contents (i.e. if the stream has at most 500 characters you'll need to set up the Length of the column to 500 in the Data Conversion transformation)
Another option could be using a Derived Column to perform both casts in a single transformation (haven't tried this solution).