Get Data Moving 1
Results 1 to 2 of 2

Thread: SQL Server Tables automatically created with string types DT_WSTR

  1. #1
    A8User is offline Junior Member
    Join Date
    Dec 2012
    Posts
    17
    Rep Power
    0

    Question SQL Server Tables automatically created with string types DT_WSTR

    Hi,

    I am in the process of setting up Attunity CDC for Oracle with SQL Server 2012 as a target. I have all the data capture working as intended, and am satisfied with this functionality.

    However, I have a question about the tables that are automatically created within SQL Server 2012 from the Attunity CDC Designer. These tables are created with every string field as a DT_WSTR type field, instead of a regular DT_STR field. My initial load from the Oracle source, however, brings in all strings as the regular DT_STR. I know I can create a data conversion for each of these fields and convert them to Unicode Strings get around this issue, and this is what I have been doing with success- but this is a burdensome process, especially when I have to convert back from Unicode String to Regular String to move the data out of these tables and into other tables.

    Is there a way for these SQL Server tables to be created from Attunity CDC Designer with the strings as a DT_STR? This would save me a lot of time, as this data conversion and remapping of fields is the most time consuming task in this process, and when I am doing this for hundreds of tables that time really adds up.

    To illustrate my process (simplified version of initial load):

    1. Turn on Attunity CDC for the target table. This creates a table in my SQL Server (CDC Database) for each table selected.
    2. I have been creating a target table in my ODS database by copying the script from these autogenerated tables.
    3. I create the initial load from an Oracle Source into the related ODS table.
    3A. I have to create a data conversion for each String field-->Unicode String field from the Oracle Table into the ODS target table.
    4. If I want to move to another SQL Server Table (Not Auto Generated from CDC) I need to use another data conversion to switch back to DT_STR field.

    Any thoughts on this?

    Thanks,

    Mark

  2. #2
    Gadi.Farhat is offline Development Manager
    Join Date
    Mar 2009
    Posts
    255
    Rep Power
    11
    Hi Mark,

    The reason all the string columns are mapped to unicode columns in SQL Server is, the difference between the source Oracle database codepage and the target SQL Server codepage. If you are sure that the target SQL Server database character encoding can store all the data stored in your source Oracle database, you may change the target column data-type using the CDC designer wizard during the configuration of the CDC instance.

    Regards,

    Gadi
    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •