I was working on creating a data migration project using SSIS in SQL Server 2005. The problem was between related column in different tables that had different data types and this fact would not allow SSIS process to continue de data migration. The difference between a string and text is common also among other languages like Ruby.
Here is the error message:
column can’t be inserted because the conversion
between DT_STR and DT_TEXT is not supported
The solution is simple and usually the same when there is an error message in SSIS about conversions. I have to add a data transformation to the migration steps.
- Add Data Flow Transformations > Data Conversion
- Change to Data Type to the required data type, in this case: DT_TEXT.
- Link process back again and test.
That should be it. Not very complicated or difficult to fix but hopefully can help someone else.