Re: Conversion error

anonymous_at_discussions.microsoft.com
Date: 08/09/04 

Date: Mon, 9 Aug 2004 11:00:57 -0700

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 spread*** 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 spread*** 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 spread*** 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)

>-----Original Message-----
>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
spread*** 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@discussions.microsoft.com> wrote in
message
>news:1c0f01c47c9b$dbd91a70$a301280a@phx.gbl...
>> 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
>> work*** 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 work***, 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
>>
>> >-----Original Message-----
>> >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@discussions.microsoft.com> wrote
in
>> message
>> >news:1c2101c47c2a$764af0f0$a601280a@phx.gbl...
>> >> 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.
>> >
>> >
>> >.
>> >
>
>
>.
>