MS SQL Server DTS - SSIS and unicode data types

Author

SSIS and unicode data types

Todd C

2006-02-13, 8:24 pm

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?

Allan Mitchell

2006-02-13, 8:24 pm

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:
>
> 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

2006-02-14, 1:23 pm

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

"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
>
>
>
>

Allan Mitchell

2006-02-19, 8:37 pm

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 o
n 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=2
0attachment

Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com
[color=darkred]
> 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
>
> "Allan Mitchell" wrote:
>

Todd C

2006-02-21, 9:23 am

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

"Allan Mitchell" wrote:

> 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.[color=darkred]
>
>
> 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
>

Allan Mitchell

2006-02-25, 9:46 am

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
[color=darkred]
> 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
> "Allan Mitchell" wrote:
>

opalcomp

2006-04-02, 9:27 am

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.

--
opalcomp
------------------------------------------------------------------------
opalcomp's Profile: http://www.dbtalk.net/m97
View this thread: http://www.dbtalk.net/t284504

Allan Mitchell

2006-04-03, 8:26 pm

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

> 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.

Sponsored Links