Re: SSIS and unicode data types

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.


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, 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
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?

.

Relevant Pages

  • Re: Multiple Columns in Rowset?
    ... I have the transform set up to insert the records from Excel to SQL Server. ... The only column that isn't inserted is CardholderID. ... the SQL Task to get the CardholderID and insert it into the Destination. ...
    (microsoft.public.sqlserver.dts)
  • Re: SSIS and unicode data types
    ... Before you do anything double click on the path between the source and the destination. ... One extracts from a table and dumps into an Excel ... whcih extracts from a SQL Server table and dumps ... file and assigns the appropriate data types, ...
    (microsoft.public.sqlserver.dts)
  • Re: SSIS and unicode data types
    ... I took another look at the package ... ... It left SQL Server as a VARCHAR and went into Excel, ...
    (microsoft.public.sqlserver.dts)
  • Re: Importing data from excel
    ... SQL Server as the destination. ... linked server to the Excel workbook and then use T-SQL to pull the data. ...
    (microsoft.public.sqlserver.tools)
  • excel import
    ... I'd like to import rows from an Excel File to an SQL Server 2005 table. ... One column of my table is 500 varchar big and my Excel column contains ... specific column is only 255 letters long. ...
    (microsoft.public.sqlserver.dts)