SSIS and unicode data types

#11

Hello IPnewdevdude,

Don't go changing your destination datatypes at all. You are right that
char == NON UNICODE and NCHAR == UNICODE.

Like I said the drivers will interpret what the datatypes are. They can
be wrong. By default a text file is UNICODE (DT_WSTR). You can see the
datatypes in the mappings page I mention or in the advanced editor.

Once you establish which column is not matching datatypes then you can employ
one of the methods I mentioned to remedy it.

Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Quote:

Wow!

Many thanks for your speedy response!

So you have both a tab delim file and an Excel Spreadsheet?

Yes, I'm dealing with the original tab delim text file and then
imported the data into an Excel spreadsheet to attempt to do some
calculations with the raw data.

Because the original source (flatfile) has strings of varying length
in several columns, we are noticing that Excel is dropping characters
beyond 255...which is no good for our db. So, we don't think we can
use the Excel file as the source for the import.

We would prefer to import from the tab delimited flat file.

Therefore, we attempted to run the SSIS on the flat file and on the
excel datatable...but we were unsuccessful on both attempts!

The unicode error is popping up. We're unclear whether we've
appropriately matched the destination column data types in the SQL
Server DB.

For the Excel Source Adapter when you have set it up go to the
advanced editor and look at what the Output column datatypes are.
DT_WSTR == UNICODE.

Does that mean our destination column data type is supposed to be
char(10) or nchar(10)? I thought nchar(10) was unicode and that the
SSIS will convert the string into the datatable?

Now you can either change it in here or you can use a Data Conversion
Transform to do it for you in the pipeline. DT_STR is NON-UNICODE.

Another way to check the datatypes that the pipeline expects is when
you are doing the mappings between columns if you hover over the
column at which you want to look it will tell you what it thinks the
datatype is.

Yes, we are transforming a string (NN.NN) (representing a dollar
amount) to a number (NN.NN) in the database....which have selected
numeric(4,2) Is that a correct datatype?

If you chage the datatypes after designing with a different on it may
not refresh so you may need to redo the transform/destination/source
from scratch.

For the Flat File. If when setting up your Flat file connection
manager you go to the advanced tab there is a button there for
"Suggest Types". You can either use this to help figure out what the
datatypes of the file should be or you can go through and change them
yourself. By default I think they are DT_WSTR

For the Flat File in the pipeline you could also use a Data
Conversion Transformation.

Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com
Could you please post steps required?

I am having the same problem using SSIS. I've been through the
documentation on line for 2 days and have not been able to achieve a
very simple process.

I have an Excel spreadsheet and a tab delimited file.
The tab delimited file contains various strings representing both
text
and
decimal values. I would like to import the data into a SQL Server
2005
datatable which matches the column/row structure of the tab
delimited
file.
I am also receiving the exact same errors the other guys are
reporting, but my column types appear to match.
There is no documentation that I can locate to describe how to port
the data.

Please post the methods to:
1. Match the source file tab-delimited columns to the
transformation
process.
2. Match the transformation variables to the SQL datatable's
variables.
- Ken
"Allan Mitchell" wrote:

Hello Todd,

Before you do anything double click on the path between the source
and the destination. Have a look at what the path metadata thinks
is coming from the source.

What i would do is remove any paths between the Source and the
destination Now add back a path.

In the Advanced properties for both the source adapter and the
destination adapter have a look at the columns that are giving you
the problem.

My guess is that one of them is

DT_STR and the other is DT_WSTR

You would use a Data Conversion transform to do the conversion
between datatypes

Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com
I have two simple DTS packages in version 2000 that are about as
simple as they come. One extracts from a table and dumps into an
Excel file, the other takes the Excel file and appends it into the
table from which it came. These took all of about two minutes to
create usine DTS in SQL Server 2000.

I have tried to re-create them in SSIS 2005 and get errors when
trying to run them. Using the Import/Export wizard, the packages
get created OK. The first one, whcih extracts from a SQL Server
table and dumps into Excel works OK. But the one that takes the
Excel data and loads it back into SQL Server gives this error on
any column defined as VARCHAR:

Column "Name" cannot convert between unicode and non-unicode
string data types.

So, basicaly what I am seeing is that the wizard creates the Excel
file and assigns the appropriate data types, but then cannot
figure out how to get that same data back!

I have tried using a Data Cpnversion transform but any data type I
try to assign does not work.

How do I get SQL Server to accept data from a Text column in
Excel?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SSIS and unicode data types

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: SSIS and unicode data types - 04-18-2006 , 06:08 AM