August 9, 2011 at 10:40 am
I have a data flow task that moves data from 1 table to another as an archive, then truncates the source table and inserts new data. The 2 tables (source and archive) are identical and built using the same script, just a change in the table name. When ran, the task fails on the first object which is an OLE DB Source. The error points to a column that is a NUMERIC(20,5) NULL. The destination column has the same name and type, however, the error message points to some data conversion occurring in memory not the destination. Any help is greatly appreciated:
[Sales_Delinq Table in the DELINQUENT database [1]] Error: There was an error with output column "REFUND_DELINQUENT_PENALTY_AMOUNT" (196) on output "OLE DB Source Output" (11). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
Preliminary queries show data is well under the PRECISION value, but all data fills in the SCALE value with suffixed zeros. A change to measure character length shows that the largest is "11" characters which I would assume is a negative sign and 5 characters OR 6 (positive) charcters to the left of the decimal point and 5 to the right.
Thank You In Advance.
JT
August 9, 2011 at 10:48 am
When pulling the data, are you using a stored proc?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 9, 2011 at 10:48 am
Answered my own question, but feel it needs a post. In case someone else has the issue.
Apparently, the data flow task was created from a copy and the meta data from the previous objects were carried over. Once I went into the object conenctor, data viewer, meta data - I saw where the precision and scale was set to much lower (5,2).
solution was to delete the OLE DB Source and recreate it. That reset the meta data and all is good to go.
JT
August 9, 2011 at 10:50 am
Aha - I have encountered that same problem too (copied data source ...). Glad you got it fixed.:-)
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply