We help IT Professionals succeed at work.

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

Medium Priority
3,861 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:
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.