SSIS and unicode data types

[Todd C]

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

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?

[Allan Mitchell]

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

Post by Todd C
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

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?

[Todd C]

Allan, thanks for getting back. I took another look at the package ...

All fields that went out of SQL Server as VARCHAR to Excel (in another
package) are coming back into the SSIS package as DT_NTEXT. The OLE Db
connection to the Server expects those column as DT_STR. So I simply need to
put in a Data Conversion Transform that will take it from DT_NTEXT to DT_STR.

BUT ...
When I do that, there is now a red X in the Data Conversion (indicating an
error) that states: "Conversion from DT_NTEXT to DT_STR is not supported".

My beef (With Microsoft) is two-fold:
1. I created this exact same package in DTS 2000 inside of a minute and it
worked flawlessly first time and every time. Why cann't the 'improved' SSIS
do the same thing.
AND
2. It left SQL Server as a VARCHAR and went into (Microsoft) Excel, then it
comes back from Excel as a datatype that cannot be converted back to VARCHAR?
What's up with that?

I appologize if I sound a bit frustrated with this issue. I have been
battling it for several days and nothing seems to work. I very much
appreciate your response and look forward to any further input you have.

Todd

Post by Allan Mitchell
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

Post by Todd C
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

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?

[Todd C]

Thanks for the tips.

However, my varchar columns are at most 50 characters. They will never be
over the 255 threshold to make them be seen as DT_NTEXT. Besides, the
destination column in the SQL database destination is only varchar(50) or
less.

I can see all the metadata definitions by opening the data flow paths,
sources, transforms, and destinations. I did not see any attachement in your
post.

By the way, where are these datatypes coming from? Is there any
documentation about what 'conversions' are allowed? I'm used to working with
char, varchar, nvarchar, etc and now I am thrown "DT_NTEXT", "DT_STR" and the
like.

So, the question still remains: How do I get an Excel column of datatype
DT_WSTR into a SQL column of datatype varchar? Note that the Excel column was
derived from the SQL column in the first place, and that it works flawlessly
in DTS and SQL 2000.

Thanks for your help.
Todd C

Post by Allan Mitchell
Hello Todd,
Ok So here is what I did
I took an Excel source
I made sure that a column had > 255 chars in it otherwise the adapter sees it as a DT_WSTR. The source adapter now sees the column as DT_NTEXT.
NOTE: if you hover over the column in the columns menu of the source adapter you will see it says DT_NTEXT. Once you have joined the path to a downstream component, double click on the path and look at the metadata to see what it says is the datatyoe of the column.
What I then did is I converted the column to a DT_WSTR 2000. I added a couple of derived columns to tell me some things about the data and It came back with no errors. See attachment
Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Post by Todd C
Allan, thanks for getting back. I took another look at the package ...
All fields that went out of SQL Server as VARCHAR to Excel (in another
package) are coming back into the SSIS package as DT_NTEXT. The OLE Db
connection to the Server expects those column as DT_STR. So I simply
need to put in a Data Conversion Transform that will take it from
DT_NTEXT to DT_STR.
BUT ...
When I do that, there is now a red X in the Data Conversion
(indicating an
error) that states: "Conversion from DT_NTEXT to DT_STR is not supported".
1. I created this exact same package in DTS 2000 inside of a minute and it
worked flawlessly first time and every time. Why cann't the 'improved' SSIS
do the same thing.
AND
2. It left SQL Server as a VARCHAR and went into (Microsoft) Excel, then it
comes back from Excel as a datatype that cannot be converted back to VARCHAR?
What's up with that?
I appologize if I sound a bit frustrated with this issue. I have been
battling it for several days and nothing seems to work. I very much
appreciate your response and look forward to any further input you have.
Todd

Post by Allan Mitchell
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

Post by Todd C
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

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?

[Allan Mitchell]

Hello Todd,

DT_* are the datatypes for the pipeline

DT_BOOL A Boolean value.
DT_BYREF_BOOL A pointer to a Boolean value.
DT_BYREF_CY A pointer to a currency value. This data type is an 8-byte signed
integer with a scale of 4 and a maximum precision of 19.
DT_BYREF_DATE A pointer to a date structure that consists of year, month,
date, and hour.
DT_BYREF_DBDATE A pointer to a date structure that consists of year, month,
and date.
DT_BYREF_DBTIME A pointer to a time structure that consists of hour, minute,
and second.
DT_BYREF_DBTIMESTAMP A pointer to a timestamp structure that consists of
year, month, hour, minute, second, and fraction.
DT_BYREF_DECIMAL A pointer to an exact numeric value with a fixed precision
and a fixed scale. This data type is a 12-byte unsigned integer with a separate
sign, a scale of 0 to 28, and a maximum precision of 29.
DT_BYREF_FILETIME A pointer to a 64-bit value that represents the number
of 100-nanosecond intervals since January 1, 1601.
DT_BYREF_GUID A pointer to a GUID.
DT_BYREF_I1 A pointer to a 1-byte, signed integer.
DT_BYREF_I2 A pointer to a 2-byte, signed integer.
DT_BYREF_I4 A pointer to a 4-byte, signed integer.
DT_BYREF_I8 A pointer to an 8-byte, signed integer.
DT_BYREF_NUMERIC A pointer to an exact numeric value with a fixed precision
and scale. This data type is a 16-byte unsigned integer with a separate sign,
a scale of 0 to 38, and a maximum precision of 38.
DT_BYREF_R4 A pointer to a single-precision floating-point value.
DT_BYREF_R8 A pointer to a double-precision floating-point value.
DT_BYREF_UI1 A pointer to a 1-byte, unsigned integer.
DT_BYREF_UI2 A pointer to a 2-byte, unsigned integer.
DT_BYREF_UI4 A pointer to a 4-byte, unsigned integer.
DT_BYREF_UI8 A pointer to an 8-byte, unsigned integer.
DT_BYTES A binary data value. The length is variable and the maximum length
is 8,000 bytes.
DT_CY A currency value. This data type is an 8-byte signed integer with a
scale of 4 and a maximum precision of 19.
DT_DATE A date structure that consists of year, month, date, and hour.
DT_DBDATE A date structure that consists of year, month, and date.
DT_DBTIME A time structure that consists of hour, minute, and second.
DT_DBTIMESTAMP A timestamp structure that consists of year, month, hour,
minute, second, and fraction.
DT_DECIMAL An exact numeric value with a fixed precision and a fixed scale.
This data type is a 12-byte unsigned integer with a separate sign, a scale
of 0 to 28, and a maximum precision of 29.
DT_EMPTY A data type that does not have a value.
DT_FILETIME A 64-bit value that represents the number of 100-nanosecond intervals
since January 1, 1601.
DT_GUID A globally unique identifier (GUID).
DT_I1 A 1-byte, signed integer.
DT_I2 A 2-byte, signed integer.
DT_I4 A 4-byte, signed integer.
DT_I8 An 8-byte, signed integer.
DT_IMAGE A binary value with a maximum size of 231-1 (2,147,483,647) bytes.
DT_NTEXT A Unicode character string with a maximum length of 2^30-1 (1,073,741,823)
characters.
DT_NULL A data type with a value of NULL.
DT_NUMERIC An exact numeric value with a fixed precision and scale. This
data type is a 16-byte unsigned integer with a separate sign, a scale of
0 to 38, and a maximum precision of 38.
DT_R4 A single-precision floating-point value.
DT_R8 A double-precision floating-point value.
DT_STR A null-terminated ANSI/MBCS character string.
DT_TEXT An ANSI/MBCS character string with a maximum length of 231-1 (2,147,483,647)
characters.
DT_UI1 A 1-byte, unsigned integer.
DT_UI2 A 2-byte, unsigned integer.
DT_UI4 A 4-byte, unsigned integer.
DT_UI8 An 8-byte, unsigned integer.
DT_WSTR A null-terminated Unicode character string.

In my package I happily took a DT_NTEXT attribute from Excel and converted
it to a DT_WSTR datatype.

Maybe you can send me the spreadsheet and the destination definition and
I can have a look


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

Post by Todd C
Thanks for the tips.
However, my varchar columns are at most 50 characters. They will never
be over the 255 threshold to make them be seen as DT_NTEXT. Besides,
the destination column in the SQL database destination is only
varchar(50) or less.
I can see all the metadata definitions by opening the data flow paths,
sources, transforms, and destinations. I did not see any attachement
in your post.
By the way, where are these datatypes coming from? Is there any
documentation about what 'conversions' are allowed? I'm used to
working with char, varchar, nvarchar, etc and now I am thrown
"DT_NTEXT", "DT_STR" and the like.
So, the question still remains: How do I get an Excel column of
datatype DT_WSTR into a SQL column of datatype varchar? Note that the
Excel column was derived from the SQL column in the first place, and
that it works flawlessly in DTS and SQL 2000.
Thanks for your help.
Todd C

Post by Allan Mitchell
Hello Todd,
Ok So here is what I did
I took an Excel source
I made sure that a column had > 255 chars in it otherwise the adapter
sees it as a DT_WSTR. The source adapter now sees the column as
DT_NTEXT.
NOTE: if you hover over the column in the columns menu of the source
adapter you will see it says DT_NTEXT. Once you have joined the path
to a downstream component, double click on the path and look at the
metadata to see what it says is the datatyoe of the column.
What I then did is I converted the column to a DT_WSTR 2000. I added
a couple of derived columns to tell me some things about the data and
It came back with no errors. See attachment
Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Post by Todd C
Allan, thanks for getting back. I took another look at the package ...
All fields that went out of SQL Server as VARCHAR to Excel (in
another package) are coming back into the SSIS package as DT_NTEXT.
The OLE Db connection to the Server expects those column as DT_STR.
So I simply need to put in a Data Conversion Transform that will
take it from DT_NTEXT to DT_STR.
BUT ...
When I do that, there is now a red X in the Data Conversion
(indicating an
error) that states: "Conversion from DT_NTEXT to DT_STR is not supported".
1. I created this exact same package in DTS 2000 inside of a minute and it
worked flawlessly first time and every time. Why cann't the
'improved'
SSIS
do the same thing.
AND
2. It left SQL Server as a VARCHAR and went into (Microsoft) Excel, then it
comes back from Excel as a datatype that cannot be converted back to VARCHAR?
What's up with that?
I appologize if I sound a bit frustrated with this issue. I have been
battling it for several days and nothing seems to work. I very much
appreciate your response and look forward to any further input you have.
Todd

Post by Allan Mitchell
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

Post by Todd C
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

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?

[opalcomp]

There is obviously a significant bug with SQL Server 2005 import/export
I am currently downloading the beta version of a Service Pack 1 fo
SS2005. After the 250 MB download, I will retry import and hope neve
again to see a message about converting between unicode an
non-unicode

--
opalcom
-----------------------------------------------------------------------
opalcomp's Profile: http://www.dbtalk.net/m9
View this thread: http://www.dbtalk.net/t28450

[Allan Mitchell]

Hello opalcomp,

I am late to this thread so could you maybe please restate your issue and
I will see if I can repro your errors. Are you having the issue in the Import/Export
wizard and/or the designer?

Thanks


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

Post by opalcomp
There is obviously a significant bug with SQL Server 2005
import/export.
I am currently downloading the beta version of a Service Pack 1 for
SS2005. After the 250 MB download, I will retry import and hope never
again to see a message about converting between unicode and
non-unicode.

[IPnewdevdude]

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

Post by Allan Mitchell
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

Post by Todd C
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

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?

[Allan Mitchell]

Hello IPnewdevdude,

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

OK


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.

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.

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

Post by IPnewdevdude
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.
1. Match the source file tab-delimited columns to the transformation process.
2. Match the transformation variables to the SQL datatable's
variables.
- Ken

Post by Allan Mitchell
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

Post by Todd C
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

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?

[IPnewdevdude]

Wow!

Many thanks for your speedy response!

Post by Allan Mitchell
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.

Post by Allan Mitchell
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?

Post by Allan Mitchell
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?

Post by Allan Mitchell
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

Post by IPnewdevdude
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.
1. Match the source file tab-delimited columns to the transformation process.
2. Match the transformation variables to the SQL datatable's variables.
- Ken

Post by Allan Mitchell
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

Post by Todd C
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

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?

[Allan Mitchell]

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

Post by IPnewdevdude
Wow!
Many thanks for your speedy response!

Post by Allan Mitchell
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.

Post by Allan Mitchell
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?

Post by Allan Mitchell
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?

Post by Allan Mitchell
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

Post by IPnewdevdude
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.
1. Match the source file tab-delimited columns to the
transformation
process.
2. Match the transformation variables to the SQL datatable's variables.
- Ken

Post by Allan Mitchell
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

Post by Todd C
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

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?