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?

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

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,

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

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.

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

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

Wow!

Many thanks for your speedy response!

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.

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?

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?