CDC Source tool in SSIS - varchar data mapping issue

• James

  SSC Veteran

  Points: 200

  More actions

  November 5, 2020 at 4:05 am

  #3804484

  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.

   

  Other information:

  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?

  Thanks.

• Site Owners

  SSC Guru

  Points: 80342

  More actions

  November 6, 2020 at 4:10 am

  #3804824

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.

• cam.t

  Newbie

  Points: 1

  More actions

  March 1, 2021 at 4:03 am

  #3851489

  Has this issue being addressed? I just ran an update to VS2017 the issue still there. I even modified all versions on local machine for the Mapping of char & varchar to DT_WSTR to DT_STR and the External Columns still show as DT_WSTR.

• James

  SSC Veteran

  Points: 200

  More actions

  March 5, 2021 at 5:36 am

  #3853640

  I have not found a solution.

  I have upgraded to VS2019 and the latest SSIS extension. I searched my entire C drive for different copies of the "SqlClientToSSIS.XML" and updated them all to map varchar to DT_STR, but this has had no effect. Very frustrating.