We have an SSIS package that is going to generate files to be consumed by Google Big Query. The files will be gzipped .tsv files.
One of the requirements is that the file be UTF-8. We have set this in the flat file destination so that it's 65001 - UTF-8. After this, the generated gzip files are consumed to Big Query correctly.
The problem now is that some fields have character lengths of up to 21,000 characters. The DT_WSTR doesn't allow for this size.
Changing the flat file destination field to DT_NTEXT yields the following error message
Error: 0xC020802E at 14_3 Data Flow into MyTSV, Flat File Destination MyDestination : The data type for "Flat File Destination MyDestination.Inputs[Flat File Destination Input].Columns[Value]" is DT_NTEXT, which is not supported with ANSI files. Use DT_TEXT instead and convert the data to DT_NTEXT using the data conversion component.
All solutions I have read involve converting back to DT_WSTR or changing the code page back to 1252 which is not an option due to the Big Query code page requirement and the length of the data. Is there another solution for this problem?