I'm trying to introduce myself to the change data capture items in the SSIS Toolbox and have hit a problem with the CDC Source tool. I've been using this tutorial as a guide: https://www.mssqltips.com/sqlservertip/5815/sql-server-integration-services-ssis-cdc-tasks-for-incremental-data-loading/
So I have a source table with varchar data types in SQL Server, but in the CDC Source Advanced Editor it maps the varchars to "Unicode string [DT_WSTR]" by default. This causes a problem because the SQL Server destination is also a varchar and it can't load the unicode type into a varchar.
Now of course I can manually set the SSIS data type mapping to be "string [DT_STR]" but this will be a huge pain when I will be working with hundreds of varchar fields. Same thing for converting the DT_WSTR to DT_STR in the Data Flow.
I was searching for an answer yesterday about default data type mappings and found a website that pointed me to an XML file that defines the default mapping: https://www.red-gate.com/simple-talk/sql/ssis/working-with-ssis-data-types/
I checked the MSSQLToSSIS10.XML and SqlClientToSSIS.XML files on my PC and they already have varchar mapped to DT_STR.
I also testing using the same table with a regular "OLE DB Source" and it correctly maps the varchars to DT_STR. So the problem seems to be specific to either the CDC Source tool, or the fact that it uses the SqlClient Data Provider instead of the OLE DB Provider.
Database: SQL Server 2017
Development Environment: Visual Studio 2017 (v15.9.28), SSDT v15, SSIS Designer v14
Has anyone got any thoughts as to what I can try next?