Re: Conversion error

From: Bernie (anonymous_at_discussions.microsoft.com)
Date: 08/09/04 

Date: Mon, 9 Aug 2004 11:03:29 -0700

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