SSIS 2005: How import an "ANSI" file?  Unicode and Code Page Errors!

Posted on 2012-08-15
Last Modified: 2012-08-29
I have a comma-delimited text file I'm trying to import using SSIS.

A program called Notepad++ says the properties of the file are:
Normal text file

I wrote an SSIS package to import using default code page 1252 (ANSI - Latin I) to varchar columns and the data is corrupted:

insider’s look (file)
insider�s look (after import VARCHAR)

universe — assembling (file)
universe � assembling (after import VARCHAR)

Research on the web says that ANSI is a misnomer and it just really means the MSB is set to store another 128 characters.



1. Because the file is "ANSI" do I have to import alter my destination table to have NVARCHAR column data types?   (I believe NVARCHAR is the correct data type I need to use, anyway.)
2. Is there any way to keep using VARCHAR columns and use the SSIS "Data Conversion" Data Flow Transformation to solve this?
3. Since the file is "ANSI" I assume I DO NOT set the file connection to "Unicode=True," right?  When I set Unicode=True I get the weirdest error: "The specified header or data row delimiter "Comma {,}" is not found after scanning 2097512 bytes of the file.  The file is obviously comma-delimited and I don't get this error if Unicode=False.
4. Since the file is "ANSI" I assume I DO NOT set the CodePage to 65001 (UTF-8)?    If I set CodePage=65001 then I get this weird error immediately (in the Flat File Source task) even though I changed by database column datatype to NVARCHAR: The data type for "output column ""MyDataColumn"" (567)" is DT_NTEXT, which is not supported with ANSI files. Use DT_TEXT instead and convert the data to DT_NTEXT using the data conversion component.
5. What set of properties do I use to pull off this simple import???
This seems like it should be really easy--something that's been done thousands of times by others--but I've fought it and Googled it now for two hours and need some help.

Supporting information:
CodePage=65001 (or 1252)
ConnectionString=**my path**
Unicode=False (or True)

Local: English (United States)
Unicode not checked
Code page=1252 (ANSI - Latin I)
Question by:ZuZuPetals
    1 Comment
    LVL 65

    Accepted Solution

    I had a similar issue with characters in a file not importing into SSIS, and we ended up...
    Opening the file in UltraEdit
    Viewing the file in Hex Code to see by-character what character SSIS was choking on
    Reported it to the source of the data, who removed it from the job that created the files.  Problem solved.

    No idea if this helps your situation, but I'll throw it out there just in case.

    Good luck.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Viewers will learn how the fundamental information of how to create a table.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now