Not able to insert string from flat file into column with datatype numeric in SSIS .

Tried Data Conversion and other things but string with input column width 15 from flat file is not getting inserted into column having data type numeric(14,4) and length 9 .

000016723440012 ==> In Db the entry should be error with this data in the specific column

000000000341200 ==> In Db the entry should be fine  with this data in the specific column

 .......i used DT_STR while picking it up and then inserting into destination ...then executing an SQL task to update the column using an expression to cast it as numeric ...Generally this works fine except for the former case .

DT_Numeric was not working for me ...only DT_STR with huge output column width was working fine

Error - Conversion failed because the data value overflowed the specified type
Gopinath ChAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Arifhusen AnsariBusiness Intelligence Developer and AnalystCommented:

I have gone through your issue.

First let's talk why you are getting error for 000016723440012.

If you remove all the zero from your data it will like 16723440012 and length is "11" . So when you try to cast as NUMERIC(14,4), it will not
insert a decimal point. But it will consider 16723440012  data before decimal point and for NUMERIC(14,4)  14-4-=10 must be the length of data before decimal point but in your case it's 11 and so its throwing error.

Now let's come to solution.

You can add the derived column task in your package.

Put a expression as below. Replace the "Data" with the column you have.

(DT_NUMERIC,14,4)((DT_I8) Data / 10000.00).

I have attached the sql script , if you want to understand the behavior of Numeric data type.

DECLARE @Data AS NVARCHAR(50)='000016723440012'

-- Error
--SELECT LEN('16723440012'),CAST(@Data AS NUMERIC(14,4))
-- With out error
SELECT LEN('16723440012'),CAST(@Data AS NUMERIC(15,4))

-- Final Result

SELECT LEN('16723440012'),CAST(CAST(@Data AS bigint)/10000.00 AS NUMERIC(14,4))

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Arifhusen AnsariBusiness Intelligence Developer and AnalystCommented:
As there is no other comments and the solution provided seems good.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.