BODS - SQL Server VARCHAR(Max) source field to target VARCHAR [SOLVED]

Scenario: We had this scenario, where the SQL server database column in our source table was created as VARCHAR(MAX), in order to accommodate large descriptions. This field is to be stored into a VARCHAR(65000) field on the target table. 

Issue: When importing this VARCHAR(MAX) source field, since BODS doesn't have the same datatype defined, this gets imported as LONG datatype in BODS. When you try to map this LONG filed to target VARCHAR field, there is a conversion error that comes up. 

Resolution: The resolution to this problem is to convert the LONG datatype identified by BODS to a VARCHAR field. The CAST. and other commonly used Conversion functions are not capable enough to handle this scenario. We referred to the BODS designer documentation and found that there are two functions to convert Long datatypes to Varchar and vice-versa, 

• LONG_TO_VARCHAR(TableName.ColumnName, IndexPosition, Length) - Converts Long datatype to Varchar. 

             The function expects three parameters, 

1. TableName.ColumnName, suppose your table is STUDENTS and column name is REMARKS, This part of the function will be ' STUDENTS.REMARKS '.
2. Maximum Size - Second argument is the maximum size of the long column to be considered for conversion.
3. Starting Position(Optional argument) - The third argument would be the Index position to start from for the conversion.

Screenshot from Reference Guide,

        

        Example - 

            LONG_TO_VARCHAR( <column_name>, <max_size>, <start_position> )

            LONG_TO_VARCHAR( STUDENT.REMARKS, 10000, 1) 

        The above function says, convert STUDENT.REMARKS field into a varchar datatype from Long, starting from index position 1 and go up till string index position of 10000. This converted field can now be stored into a VARCHAR field in transform/target.