How to map varchar(max) column in SCD transform while doing ETL process in SSIS project?

Question

In Source table AAT_TECHNICIAN column has datatype varchar(max) and in destination dimension table having column TechnicianName with datatype varchar(max) then SCD transform done to load from source to destination. After execute package error came. For other transform also same error is coming.

Error: The "Slowly Changing Dimension input Columns[AAT_TECHNICIANS]" has a long object data type of DT_TEXT, DT_NTEXT, or DT_IMAGE, which is not supported.

Answer 1

As you can see, SSIS SCD doesn't support these datatypes. There are probably two options:

1. Use a different methodology to load the target table i.e. don't use the SSIS SCD functionality
2. Copy the source data into a temporary table that has datatypes supported by SSIS SCD and load it from there