?
Solved

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

Posted on 2012-08-15
1
Medium Priority
?
3,532 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)
0
Comment
Question by:ZuZuPetals
1 Comment
 
LVL 66

Accepted Solution

by:
Jim Horn earned 1500 total points
ID: 38297042
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.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

850 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