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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy