locked
The value was too large to fit in the output column with ADO.NET data flow and DECIMAL data type RRS feed

  • Question

  • First of all, yes, I have seen that there are a lot of posts on this error, but none of them seem to apply.

    I’m using SQL Server 2008 R2 and I develop Integration Services projects in Visual Studio 2008. Please don’t hesitate to ask for more details.

    I have two tables which have one column of data type DECIMAL(38,15) null. There is only one record in the first table:

    -132748164120001200.000000000000000

    There are zero records in the other table, which I try to transfer data to. The value obviously fits since it is to be found in the first table, but when I transfer it, I get the ‘The value was too large to fit in the output column’-error.

    My guess is that it is related to some bug in Visual Studio, since if I do an insert from the first table to the second in SQL Server Management Studio it works fine. If I use OLE DB instead of ADO.NET it also works fine.


    Wednesday, January 14, 2015 10:21 AM

Answers

All replies

  • what datatype is field showing in SSIS?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, January 14, 2015 10:59 AM
  • Which component errors out? Source or destination?

    The obvious solution would be to use the OLE DB component instead ;)


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Wednesday, January 14, 2015 12:54 PM
  • Thanks for taking time to look at my question.

    The Advanced Editor shows that the Data Type is numeric[DT_NUMERIC] with Precision 38 and Scale 15 in both External Columns and Output Columns for both Source as well as Destination.

    Wednesday, January 14, 2015 1:15 PM
  • You are right Koen, OLE DB would be the intelligent pragmatic solution… J

    Another workaround is writing cast(Round(FieldName,5) as Numeric(38,5)) as FieldName in the select-statement in the source. I guess this has become a quest or a mission, which I would like to have answered.

    It is already in the source it errors out.

    Wednesday, January 14, 2015 1:21 PM
  • I can reproduce the issue, so you are not doing anything wrong.
    Seems like an issue with the .NET provider for SQL (since OLE DB works just fine).

    The value is only accepted when it's this big:

    -13274816412000

    Even SSMS starts freaking out (I guess  because it also uses .NET behind the scenes):


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Wednesday, January 14, 2015 1:40 PM
  • I did a bit more research and it's indeed some sort of bug.

    Connect item: SSMS "Edit first 200 rows" is <Unable to read data> for decimal(p,s) when p is large

    (of course the connect item is closed as Won't fix. Good job MS)

    Someone at SO does a good job of explaining the issue:
    How to query for rows containing <unable data="" read="" to="">in a column?</unable>

    In SSMS it's just a minor issue, but apparently in SSIS it can give issues with ADO.NET.
    So I stand with my earlier conclusion: use OLE DB instead :)</unable>


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.


    Wednesday, January 14, 2015 1:55 PM