Invalid data for type "numeric".

Question

Hi All,

We have a process in the ETL where the Ingress Data( Source ) will be loaded into our SQL Server DB ( Destination ). i am getting an error saying Invalid data for type "numeric" while loading a big table of 120 columns and 9 million Rows. So can anyone help me out on how do i find the row which is having the bad data. Please Help..!

Thanks in Advance..

Regards,

---

Balaji - BI Developer

Answer 1

You can try configuring the package to not fail on error and writing the error output to a flat file.

See this article for an example.

---

Andy Tauber
Data Architect
The Vancouver Clinic
Website | LinkedIn

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

Answer 2

Please see:

http://blog.sqlauthority.com/2009/04/09/sql-server-fix-error-msg-9803-level-16-invalid-data-for-type-numeric-data-type-mapping-for-oracle-publishers/

Answer 3

Andy

I do not think that SSIS provides a column name due to which the row is redirected,,,,,,

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 4

It doesnt provide column name but it cant be captured by creating custom component using some .NET coding

see

https://naseermuhammed.wordpress.com/tips-tricks/getting-error-column-name-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

Answer 5

 >>>but it cant be captured by creating custom component using some .NET coding

I think you meant it CAN be captured , do not you? :-)

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 6

Hi Andy,

Thanks for the Reply. But i have been using the a Stored procedure in the ETL in which i have to load around 50 tables one by one. So we have used a stored procedure to do this. Is there any alternate to find the bad data.

Regards,

---

Balaji - BI Developer

Answer 7

 >>>but it cant be captured by creating custom component using some .NET coding

I think you meant it CAN be captured , do not you? :-)

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Yes indeed :)

---

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

Answer 8

Hi Andy,

Thanks for the Reply. But i have been using the a Stored procedure in the ETL in which i have to load around 50 tables one by one. So we have used a stored procedure to do this. Is there any alternate to find the bad data.

Regards,

---

Balaji - BI Developer

If you're using Stored procedure then why not handle bad data inside it itself. Do the data type, length etc validations in SQL itself and capture error records in your log table with column information

---

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