SSIS Error converting varchar to numeric while importing csv flat file into sql table
I have flat file having column MGR_fee with data like (10.00%,12.00%,....) . On other hand i have created a stored proc where i took MGR_fee as Varchar(50) but in table this column,MGR_fee has datatype Decimal(32,6) . so i used in my proc CONVERT(DECIMAL(32,6),REPLACE(@MGR_fee,'%','')), SYNTAX WHILE INSERTING THE VALUE. But when i ran my SSIS package i got the error message "[OLE DB Command [20]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E07. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E07 Description: "Error converting data type varchar to numeric.". ". Please help me out.
October 9th, 2012 3:07am

Hi, There seems to be no issue with that statement," CONVERT(DECIMAL(32,6),REPLACE(@MGR_fee,'%',''))", Can you give some details like table structure, SP functionality, sample data..Rajkumar
Free Windows Admin Tool Kit Click here and download it now
October 9th, 2012 3:13am

HI Anshul looks like malign data in your Flat file you need to confirm that in flat file data will be like 10.00% only and nothing else [ie comma separated columns with percentage at end and rest only numerics values with only one decimal point]... if not you need to handle that in your sp ... By the is there any specific need using OLEDB command transformation ????? Hope that helps ... Kunal
October 9th, 2012 3:16am

Could be a data issue as well, execute this query to check if you have any alphanumeric value SELECT r, PATINDEX('%[a-z]%',r) ChkAlpha FROM ( SELECT Mgr_fee r From Table )rs where PATINDEX('%[a-z]%',r) <> 0 Rajkumar
Free Windows Admin Tool Kit Click here and download it now
October 9th, 2012 3:29am

Hi Raj, table structure is CREATE TABLE [dbo].[wb_Space_PreProcess]( [Mgr_Fee] [decimal](32, 6) NULL, [Perf_Fee] [varchar](50) NULL, [Date_Funded] [datetime] NULL) Stored proc: CREATE PROCEDURE [dbo].[usp_wb_Space_Load] @Mgr_Fee VARCHAR(50), @Perf_Fee VARCHAR(50), @Date_Funded DATETIME AS BEGIN INSERT INTO WBDataMart.dbo.wb_FOF_Entity_PreProcess( Mgmt_Fee , Perf_Fee , Date_Funded ) VALUES (CONVERT(DECIMAL(32,6),REPLACE(@Mgr_Fee,'%','')), CONVERT(DECIMAL(32,6),REPLACE(@Perf_Fee ,'%','')), @Date_Funded ) and MGR_fee and Perf_fee fields of cvs flat file have data like 10.00%,12.00% only there is no comma etc. I want to know which setting needs to be done while integrating with SSIS package. otherwise when i change the datatype to varchar in table structure, it is easily importing into tha table. so i just want to resolve this problem "Error converting data type varchar to numeric".
October 9th, 2012 3:31am

Hi, What is the destination table? "wb_Space_PreProcess" or wb_FOF_Entity_PreProcess If wb_FOF_Entity_PreProcess what is wb_Space_PreProcess?Rajkumar
Free Windows Admin Tool Kit Click here and download it now
October 9th, 2012 3:36am

Hi Raj, and MGR_fee and Perf_fee fields of cvs flat file have data like 10.00%,12.00% only there is no comma etc. I want to know which setting needs to be done while integrating with SSIS package. otherwise when i change the datatype to varchar in table structure, it is easily importing into tha table. so i just want to resolve this problem "Error converting data type varchar to numeric". HI Anshul there is no escape or setting to overcome unless the data in file is clean or you have handled the malicious data in your stopred procedure .. you said you have loaded the data with destination datatype as varchar right ?? just check on that table where ISNUMERIC(Mgr_Fee) is 0 you will get to know where is the problem ... Hope that helps ... Kunal
October 9th, 2012 3:39am

it gives error " Argument data type decimal is invalid for argument 2 of patindex function."
Free Windows Admin Tool Kit Click here and download it now
October 9th, 2012 3:40am

Whats the data type of MGR_FEE? If Decimal already how do you have '%' in it? I think Something is missing here..Rajkumar
October 9th, 2012 3:49am

Why do you not use a derived column transformation in your package to remove the % from MGR_FEE flat file column and convert it into Decimal (32,6). Use an expression like (DT_DECIMAL, 6) REPLACE(TRIM([MGR_FEE]),"%","") http://btsbee.wordpress.com/
Free Windows Admin Tool Kit Click here and download it now
October 9th, 2012 5:21am

Why do you not use a derived column transformation in your package to remove the % from MGR_FEE flat file column and convert it into Decimal (32,6). Use an expression like (DT_DECIMAL, 6) REPLACE(TRIM([MGR_FEE]),"%","") http://btsbee.wordpress.com/
October 9th, 2012 5:38am

Thanks to every one , my query has been resolved by using CASE WHEN ISNULL(@Mgr_Fee,'') <> '' THEN CAST((REPLACE(@Mgr_Fee, '%', '')) AS DECIMAL(32,6)) END
Free Windows Admin Tool Kit Click here and download it now
October 23rd, 2012 3:30am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics