November 5, 2020 at 4:05 am
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.
November 6, 2020 at 4:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
March 1, 2021 at 4:03 am
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.
March 5, 2021 at 5:36 am
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