Re: Conversion error

From: Allan Mitchell (allan_at_no-spam.sqldts.com)
Date: 08/10/04 

Date: Tue, 10 Aug 2004 20:29:22 +0100

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@discussions.microsoft.com> wrote in message
news:2d9401c47e61$ff921df0$a301280a@phx.gbl...
> 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@cox.net.
>
> Regards,
> Bernie Jeltema
> >-----Original Message-----
> >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 ***.
> I meant create a
> >table in SQL Server - All Character datatypes -
> structure the same as your
> >spread***
> >
> >-- 
> >-- 
> >
> >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@discussions.microsoft.com> wrote in message
> >news:2df101c47e3a$d21c4ed0$a401280a@phx.gbl...
> >> 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.
> >> >> >
> >> >> >
> >> >> >.
> >> >> >
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >