I am trying to import an Excel file using Copy Column in
the Data Transform Task. All of the columns import
successfully into varchar columns except one. This
produces the error msg "conversion invalid for datatypes
on column pair 7 (source column '2004 Cost'
(DBTYPE_WSTR), destination
column 'UOM_2004_Cost'(DBTYPE_CY))."

The source column contains both money values and text
values. I have run a VBScript macro to make sure all
values in the column are text values (a preceding
apostrophe). I also tried to change the destination
column to nvarchar to make sure it matches the
DBTYPE_WSTR.

Why won't this work? Why does the error message imply the
transform implicitly changes the destination column to
money (DBTYPE_CY)? How can I solve this so I can
transform the Excel file?

Thanks in advance.

The error suggests your source (Excel - 2004 Cost) is a character attribute
and your destination attribute (UOM_2004_Cost) is a Currency datatype.

The problem with having Character datatypes when you actually mean something
different is that most things are valid in text whereas if the definition
was correct then they possibly wouldn't be.

An example would be that if you wanted to store Salries in an attribute but
because some people have unknown salaries then you declare the attribute
with a character datatype and then you can enter NOT KNOWN. Problem being
your destination is a MONEY datatype say and NOT KNOWN will most certainly
not fit.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Bernie" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Thanks, Allan. But how can I solve or work around the
problem? This is a data warehouse application. I need to
bring everything in all the columns in the Excel
worksheet into the SQL Server database, including this
column which has both Money and Character datatypes. Once
all the data is in the DB, I can deal as needed with the
different data types in the same column.

So, my practice is to bring everything in as varchar,
then deal with the data types. But in this situation, DTS
seems to be converting the datatype of this destination
column automatically to MONEY, without my control and
contrary to my VARCHAR definition. (A different column in
the worksheet, where the data is all money type, comes in
fine as varchar. But the column I am having the problem
with, where the source system legitimately has both money
and character data, forces the destination seemingly to
be MONEY and creates this Conversion Error msg.)

I have been able to workaround by importing Excel into
Access, then importing the Access table into SQL Server
in the DTS package, but I don't know a way to use DTS to
move data from Excel to Access so the DTS package will do
the entire extract automatically when scheduled on a
nightly basis. So this workaround is not acceptable as is.

Any other ideas on how to solve this problem or create a
workaround?

Thanks again,
Bernie

The problem is not in your source, well it is sort of, but it also has to do
with the destination

SQL Server allows implicit conversions and if you have '1.1' as a string
going to a MONEY datatype then that's fine but not if you have 'SOME VALUE'
which is valid in the source and not in the destination.

The other attribute you mentions works does that have mixed data? By the
sounds of it not.

The easiest thing to do is to create a replica of your spreadsheet as a
scratch table (all character attributes). Let's face it Excel can only
handle 65K rows so the overhead is minimal.

You can then use TSQL against that table and do similar to

.................... WHERE ISNUMERIC(MyCol) = 1



--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Bernie" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Allan, thanks again. But I still need more help to get at
the root of this issue.

First, I plan to try your suggestion to "create a replica
of your spreadsheet as a scratch table (all character
attributes)." If I understand you correctly, I had
already done that. That is, I applied a VB macro to
change all values in my 15 column, 10K row spreadsheet to
text (character strings) before importing them into SQL
Server using DTS. If that is not what you meant, please
tell me what you mean and how to create a scratch table.

Second, if this does not work, I am leaning toward the
conclusion that there is a flaw (bug?) in SQL Server DTS.
If not, what else can I try? If so, how do I get
Microsoft's attention to provide a fix for this problem?

Here is the problem as I see it. As you comment, SQL
Server allows implicit conversions. I need it to follow
my explicit conversion rule, not override it.

Here is the situation and what I have done. I have
changed all source spreadsheet values to character
strings. I have defined all columns in the SQL Server
destination table as varchar. This should allow me to
bring all values into the database and handle the data
quality issues, such as inconsistent values in a column,
with tools such as TSQL ISNUMERIC. One of my 15 columns
is mixed numeric (integers) and alphanumeric. It imports
all values properly to varchar. Another column is all
money data (1.10 etc.). It too imports aqll values
properly to varchar. But the column I am having
difficulty with has a mix of money (1.10) and text
(Regional Pricing). The conversion error says I am trying
to convert the source data (DBTYPS_WSTR)

Allan, I did not quite finish the post, but I'm sure you
get the idea. I await your reply. Hopefully this time
around will solve the problem.

Thanks for your time!

Bernie

Exactly

We are nearly there now.

You have identified your problem. The column that holds both numeric and
character data has to be one or the other. If you want to keep the
character data you either convert it to a value which denotes its character
value so

UNKNOWN = -1

for example

Or you change the destination datatype.

By Scratch table I did not mean changing the XL sheet. I meant create a
table in SQL Server - All Character datatypes - structure the same as your
spreadsheet

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


<anonymous (AT) discussions (DOT) microsoft.com> wrote

Allan,

Thanks eversomuch! I will take it from here.

If you do get the problem solved in SQL Server DTS where
I can import both types of data into a single varchar
destination column, I would appreciate an email telling
me how I can download the fix.

My email address is jeltema (AT) cox (DOT) net.

Regards,
Bernie Jeltema

You canimport both types of data into a varchar column now. You cannot
import both types of data into a numeric column

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Bernie" <anonymous (AT) discussions (DOT) microsoft.com> wrote