none
Inconsistent error: Arithmetic overflow error converting varchar to data type numeric. RRS feed

  • Question

  • This package loads a text file into a staging table, and then inserts them into the fact table.  What's weird is that the package seems to raise an error every 5-10 days, and I can't figure out why.  

    This package runs every morning at about the same time in two environments, test and prod.  The failures seem to be totally random, and when it fails in one environment, it's always successful in the other environment using the exact same downloaded data file, the same package, and exact same objects and object definitions (data types, etc.).

     

    When I execute the sql statement that raised the error in Management Studio, it works fine.  If I rerun the same package with the same data file, it runs fine the next time.  

     

    What could cause this strange inconsistency?  

    The data flow task uses the following SQL statement:

     

    SELECT 

    c.CurrencyTarget

    ,CAST(Rate AS DECIMAL(18,16)) AS Rate

    ,CAST(RatePrior AS DECIMAL(18,16)) AS RatePrior

    ,CAST(REPLACE(DayRateChange, 'unch', 0) AS DECIMAL(18,16)) AS DayRateChange

    ,CAST(REPLACE(YearRatechange, 'unch', 0) AS DECIMAL(18,16)) AS YearRateChange

    ,CAST(PerUSD AS DECIMAL(18,16)) AS PerUSD

    ,CAST(PerUSDPrior AS DECIMAL(18,16)) AS PerUSDPrior

    FROM 

    TempRates r

    INNER JOIN 

    CurrencyMapping c ON r.Currency = c.CurrencySource

     

    A derived column is married in to the data, and eventually lands in the RateDate column in RatesRetrieved.  

     

    Some object definitions:

     

    CREATE TABLE [dbo].[TempRates](

    [Currency] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Rate] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [RatePrior] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [DayRateChange] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [YearRateChange] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [PerUSD] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [PerUSDPrior] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

    ) ON [PRIMARY]

     

     

    CREATE TABLE [dbo].[CurrencyMapping](

    [CurrencyID] [int] IDENTITY(1,1) NOT NULL,

    [CurrencyTarget] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [CurrencySource] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [CurrencyAddedDateTime] [datetime] NOT NULL CONSTRAINT [DF_CurrencyMapping_CurrencyAddedDateTime]  DEFAULT (getdate()),

    [CurrencyAddedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

    ) ON [PRIMARY]

     

    CREATE TABLE [dbo].[RatesRetrieved](

    [RateID] [int] IDENTITY(1,1) NOT NULL,

    [CurrencyType] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [Rate] [decimal](18, 16) NOT NULL,

    [RatePrior] [decimal](18, 16) NOT NULL,

    [DayRatechange] [decimal](18, 16) NOT NULL,

    [YearRateChange] [decimal](18, 16) NOT NULL,

    [PerUSD] [decimal](18, 16) NOT NULL,

    [PerUSDPrior] [decimal](18, 16) NOT NULL,

    [RateDate] [datetime] NOT NULL,

    [RateAddedDateTime] [datetime] NOT NULL CONSTRAINT [DF_RatesRetrieved_RateAddedDateTime]  DEFAULT (getdate()),

    [RateAddedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_RatesRetrieved_RateAddedBy]  DEFAULT ('RetrievalProcess')

    ) ON [PRIMARY]

     

     

    Thursday, August 11, 2011 3:54 PM

Answers

  • The error you've quoted sounds like a SQL Server error, not an SSIS one.  I can't tell you why it's inconsistent.

    If you want to diagnose the problem, you'd be better off moving the type conversion into SSIS and out of the SQL statement.  If a conversion fails in SSIS, you can isolate the row that caused the problem, but you can't do that in T-SQL.

    So don't cast anything in your SELECTs.  Use Derived Columns and/or Data Conversion components with error redirection to do the casting.


    Todd McDermid's Blog Talk to me now on
    Friday, August 12, 2011 12:00 AM
    Moderator