I'm developing a SSIS package to load a flat file into a table, but a warning appears on varchar fields:
Column "xyz" cannot convert between unicode and non-unicode strings data types.
The database is SQL_Latin1_General_CP1_CI_AI and the file was UTF-8, so that message makes sense.
Instead of adding filters, derived columns or whatever in the SSIS project, I could change the program that creates the flat file, so it is ANSI iso-8859-1 or Latin1 now, but the error message has not gone. I deleted the project and started a new one without success.
The Code Page needs to be '1252 (ANSI - Latin 1) for SSIS. If the code page for the file is not setup that way - then you need to add a derived columns transformation to convert to ANSI.
I've checked that it says that CodePage is 1252 in the Propierties for the flat file conection manager.
I've also checked that the CodePage has the same value for all Input Columns in the Advanced Editor for OLE DB Destination.
The DefaultCodePage is also 1252 for the database. It doesn't matter if AlwaysUseDefaultCodePage is true or false. BTW, those columns in the table are of nvarchar type.
I changed the nvarchar to varchar datatype in the destination table and this error disappeared.
Then, when the flat file has UTF-8 encoding, why I couldn't load it to nvarchar fields as this datatype is for UNICODE texts? I guess that there was another config in the middle of the flow that was bothering. Which one?
I was going to suggest making the change in the table - but I see you already did that. As to why the UTF file wouldn't load - I would guess it has to do with the code page that was selected not matching the code page for the table/columns.
It is supposed that NVARCHAR type is for Unicode, but as the database was still defined as Latin1, I'd expect a datatype bypass and store them in UTF-8 like in the source flat file.