We help IT Professionals succeed at work.
Get Started

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

3,906 Views
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
Dos\Windows
ANSI

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.

Questions:

1.

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:
Connection:
ConnectionManagerType=FLATFILE
CodePage=65001 (or 1252)
ConnectionString=**my path**
Format=Delimited
HeaderRowDelimiter={CR}{LF}
RowDelimiter=
TextQualifier=<none>
Unicode=False (or True)

Edit/General/Column:
Local: English (United States)
Unicode not checked
Code page=1252 (ANSI - Latin I)
Comment
Watch Question
SQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015
Commented:
This problem has been solved!
Unlock 1 Answer and 1 Comment.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE