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

Gopinath Ch
Gopinath Ch used Ask the Experts™
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
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Business Intelligence Developer and Analyst
Top Expert 2015

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

Arifhusen AnsariBusiness Intelligence Developer and Analyst
Top Expert 2015

As there is no other comments and the solution provided seems good.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial