Error between unicode and non-unicode string data types!

Question

Error between unicode and non-unicode string data types!

A Little Help Please

SSCarpal Tunnel

Points: 4764
More actions
April 22, 2009 at 1:10 am

#133395

SSIS package importing a flat file into a OLE DB Destination.
I am seeing the following error on the OLE DB Destination:
Error:
Validation error. Data Flow Task: Borg Classifications Table
[1298]: Columns "Column 1" and "class_code" cannot convert
between unicode and non-unicode string data types.
In the SSIS package:
Column 1
String [DT_STR]
In the DB:
Class_Code
VARCHAR(50)

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Assuming the table you're importing into is a staging table - which it should be - then change the data type of the receiving column to NVARCHAR.

Staging tables can be structured however you like; anything you can do to avoid dropped rows and improve speed is a bonus.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 2

Hi,

Firstly im not using "staging tables".

This is a on of bulk insert fro csv-->sql table.

No need for primary keys, foreign keys etc..

I have managed to fix the error above by using Data Conversion.

I now seem to be getting the following error:

Error: 0xC02020A1 at Data Flow Task, Borg_Addresses

[1004]: Data conversion failed. The data

conversion for column "Column 10" returned status value 4

and status text "Text was truncated or one or more

characters had no match in the target code page.".

Some example text from Column 10 from my CSV file:

ABERDEEN ASSET MANAGERS LIMITED A/C ABERDEEN GLOBAL UK EQUITY FUND

ABN AMRO ASSET MANAGEMENT LIMITED TRUSTEES OF THE ABN AMRO UK GROWTH FUND (6408)

ABN AMRO ASSET MANAGEMENT LTD PERSONAL - PENSION MANAGEMENT SCHEME REF: W01044R (R H WELLS ESQ)

The column in the DB is set to VARCHAR (200)

The column in the SSIS package set to String [DT_STR] 200

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Check out the NVARCHAR datatype in BOL. You will need to change the size of your VARCHAR receiving column.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 4

What does the following error mean?

TITLE: Microsoft Visual Studio

------------------------------

The preview sample contains embedded text qualifiers ("). The flat file parser does not support embedding text qualifiers in data. Parsing columns that contain data with text qualifiers will fail at run time.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

A Little Help Please (4/22/2009)
What does the following error mean?
TITLE: Microsoft Visual Studio
------------------------------
The preview sample contains embedded text qualifiers ("). The flat file parser does not support embedding text qualifiers in data. Parsing columns that contain data with text qualifiers will fail at run time.

This is a different issue to the topic of this thread. It means that you've got embedded single or double quotes in an input string which you've designated as an output column.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 6

Thanks Chris,

Im a bit lost now...

You say I got an embedded single or double quotes in an input string which I'v designated as an output column.

Im a bit new to SSIS so please can you explain in dummy terms?!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

A Little Help Please (4/22/2009)
Thanks Chris,
Im a bit lost now...
You say I got an embedded single or double quotes in an input string which I'v designated as an output column.
Im a bit new to SSIS so please can you explain in dummy terms?!

Hardly - you've been using it for more than a year!

A column value has embedded quotes in it, something like this:

J. P. Andrews, Painter & Decorator

J. P. Andrews, Berkshire's Premier Renovators

J. P. Andrews, "Berkshire's Best"

Of course, you may have defined two source columns as one destination column, which you've done before, but if I remember correctly the error message was different. Can you examine the source in say Excel?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 8

Thanks,

I have checked the csv file, i do not have any rows with " or '

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

A Little Help Please (4/22/2009)
Thanks,
I have checked the csv file, i do not have any rows with " or '

If there are definitely no rows with embedded single or double quotes visible in Excel, then you might want to confirm with something like Wordpad. How many rows and columns in the file?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 10

I have open the csv file in wordpad, no " or ' apart from those used as column deliminators.

Example data:

"ABC 9001","N","TRD","","","IN_UK","N","N","","","","","","","N","",""

"ABC 0001","N","TRD","","","IN_UK","N","N","","","","","","","N","",""

"ABC 0002","N","TRD","","","IN_UK","N","N","","","","","","","N","",""

"ABERD9001","N","TRD","","","IN_UK","N","N","","","","","","","N","",""

"ABERD0009","N","TRD","","","IN_UK","N","N","","","","","","","N","",""

"ABERD0037","N","TRD","","","IN_UK","N","N","","","","","","","N","",""

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Then I guess you're concatenating two source columns. Have you tried remapping them?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 12

A Little Help Please

SSCarpal Tunnel

Points: 4764

April 22, 2009 at 7:33 am

#982176

Thanks Chris,

I will start from scatch then come back...

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 13

Ok so the following error is occuring even before I finish setting up the flat file to OLE DB connection.

Within Flat File Connection Manager I have selected:

Format: delimited

Text qualifier: "

Header row deliniter: Comma {,}

When I click on Column i get the following error:

TITLE: Microsoft Visual Studio

------------------------------

The preview sample contains embedded text qualifiers ("). The flat file parser does not support embedding text qualifiers in data. Parsing columns that contain data with text qualifiers will fail at run time.

I have checked by opening the file in excel and note pad but I cant see any other " apart from the text qualifier...

Any more ideas?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

How many rows and columns in the flat file?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden