convert text with utf8 encoding to SQL SERVER using SSIS
I'm trying to find the best way to load a source table from MySQL (with the following column definition: text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL) into SQL SERVER using SSIS. SSIS automatically recommend "Image" data type. However it doesn't suit. Furthermore, in the "preview" dialog i get this data: "System.Byte". What's the best way to convert this column in to something more usable utilizing only SQL and MySQL transformations?
Convert to UTF-16 on MySQL. SQL Server NCHAR is UTF-16 so that should work straight away.