SSIS - Data encoding when getting data from flat file to OLE DB destination

Diogo Pedro Melo 1 Reputation point
2022-05-08T19:25:34.323+00:00

Hello,
I have a "Flat file" from which I'm getting data to pass to "Destination DB" through an "SSIS Data Flow". Once I run the package, and query the DB destination I realize there're wrong data values on the destination. (schema attached and wrong value marked in yellow)
For example, when IDCLIENT=1000 I get NAME ='AntÃ'nio' and I should get NAME = 'Antônio' as it is in the source.
I know is an encoding-related issue. but don't know how to fix it. Any help will be very appreciated!!200051-screenshot-2022-05-08-202341.jpg

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,310 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,520 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Yitzhak Khabinsky 25,731 Reputation points
    2022-05-09T00:13:16.86+00:00

    Hi @Diogo Pedro Melo ,

    Go to the Flat File Connection Manager.
    It has three options to control data encoding and Unicode characters:

    • Code page (most important setting)
    • Locale dropdown
    • Unicode checkbox.

    Please see below.

    200256-ssis-flat-file-connection-manager.jpg

    0 comments No comments

  2. ZoeHui-MSFT 35,226 Reputation points
    2022-05-09T05:45:25.983+00:00

    Hi @Diogo Pedro Melo ,

    Please configure the Flat File connection manager.

    Specify the file, locale, and code page to use. The locale is used to interpret locale-sensitive data such as dates, and the code page is used to convert string data to Unicode.

    flat-file-connection-manager

    Regards,

    Zoe


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.