SSIS package error based on database collation

Question

I have this old SSIS package I was using to load utf8-encoded csv files into a database. A very small percentage of the data had unicode characters, and people didn't particularly care if we dropped a few values to get the greater bulk into a SQL_Latin1_General_CP1_CI_AI collation table.

My SSIS package loaded the csv with codepage 65001 and had the output columns as WSTR. There was a data conversion component that converted the wide characters into 1252. Then there's an OLE DB Destination component.

This worked fine. A few columns here and there had ??? for a few kanji characters but all the latinate characters in the csv transferred well.

Now the powers that be want to expand the character handling. I made a copy of the table and put it in a database with a default encoding of Latin1_General_100_CI_AI_SC_UTF8.

I started the old loader package just to kick the tires and about 600 rows in, I got the following errors:

Error: 0xC0202009 at Data Flow Task, OLE DB Destination [503]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "OLE DB provider 'STREAM' for linked server '(null)' returned invalid data for column '[!BulkInsert].FirstName'.".
Error: 0xC0209029 at Data Flow Task, OLE DB Destination [503]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "OLE DB Destination.Inputs[OLE DB Destination Input]" failed because error code 0xC020907B occurred, and the error row disposition on "OLE DB Destination.Inputs[OLE DB Destination Input]" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
Error: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "OLE DB Destination" (503) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (516). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.
Error: 0xC02020C4 at Data Flow Task, Flat File Source [266]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
Error: 0xC0047038 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on Flat File Source returned error code 0xC02020C4.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

I've done a lot of googling on the error and have only found a couple of hits, referring to datatype mismatches - which doesn't seem entirely accurate. I expected the old package would still ??? the few kanji characters but otherwise things would work

The only thing different is the collation. I pointed the package back at a table with a SQL_Latin1_General_CP1_CI_AI default encoding, and it still worked fine.

There's not much information in the error messages. Is it because I'm getting an accent aigu or something in 1252 and literally that's invalid utf-8? And the OLE DB component is not making the translation?

Thanks

Answer 1

After more experimentation, I found that while I was allowed to edit the SSIS OLE DB Destination component and change the code page, it wouldn't stick. i.e. I could click Save after the changes, open the editor again, and none of my edits had been saved.

Searching instead for getting edits to stick, I stumbled across this:

How do I fix the Code Page in SSIS Lookup Transformation to be 65001?

And the answer was in there. On the Connection portion of the component editing, set the AlwaysUseDefaultCodePage setting to true, and change the code page to 65001 there. Then the explicit utf8 code page flows down to the column mappings.

Now the package is running.