CDC Source tool in SSIS - varchar data mapping issue

  • 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:

    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:

    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 for posting your issue and hopefully someone will answer soon.

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

  • 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.

  • 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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply