Converting nchar to int (or numeric/decimal)

Hi ,

I have a column in my extract table as nchar(3) and in the destination (the same column with diff name ) it is decimal(3,0) .....i tried to use dataconversion transformation.....i even tried to use cast/convert fn's in the SQL Command (which i use in the "Source Transformation" to get the columns from the extract table).

I tried all the ways i can and still i get the same error..:

[OLE DB Source [1]] Error: SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred. Error code: 0x80040E07. An OLE
DB record is available. Source: "Microsoft OLE DB Provider
for SQL Server" Hresult: 0x80040E07 Description: "Error
converting data type nvarchar to numeric.".

Can we actually do it...? any help would be appreciated.

thanks

ravi

you have non-numeric data in the source column that fails the conversion. Have you tried to configure the error output of the conversion transformation to re-direct error so the 'bad' rows are sent to a diffrent output where youc can inspect them?|||

Hi,

No ..No....Its Numeric in the source though the data type is nchar(3).

The thing is actually the column in the extract table has records as 110,150,160,170 and so on .....but its data type is nchar(3).The reason is this extract table comes from AS400 and while extracting As400 doesnt allow to send records if the column in the extract table on Sql Server is defined other than nchar...so have to go with nchar or nvarchar.

thanks

ravi

|||Try a derived column to cast the records. (Why not make them integers?)

(DT_I4)[Column]|||

Works fine for me:

Code Snippet

CREATETABLE #temp(test nchar(3))

INSERTINTO #temp SELECT'111'

INSERTINTO #temp SELECT'211'

INSERTINTO #temp SELECT'311'

SELECTSUM(cast(test asint))FROM #temp

DROPTABLE #temp

Adamus

|||

Adamus Turner wrote:

Works fine for me:

Code Snippet

CREATETABLE #temp(test nchar(3))

INSERTINTO #temp SELECT'111'

INSERTINTO #temp SELECT'211'

INSERTINTO #temp SELECT'311'

SELECTSUM(cast(test asint))FROM #temp

DROPTABLE #temp

Adamus

Yeah, but Ravi's source is AS400, not SQL Server. Though, the other option is to stage the data into SQL Server as it is in AS400, and then use a cast statement when pulling records from the staging table as Adamus has illustrated here.|||

Hey phil,

I extract the table from AS400 to extract_table on my extract database which is SQL Server DB , from there when i need to make some transformations then my column on the staging is decimal(3,0)....where as on the extract table it is nchar(3).

I tried similarly by declaring some variables and casting and converting ...it doensnt work..or am i doing some mistake...:

DECLARE @.X nchar(3)

DECLARE @.Y int

SET @.X ='GGG'

SELECT @.X

SET @.Y =Cast(@.X asint)

SELECT @.Y

thanks for all you views/immediate response's...

let me know if i am doing a mistake here..

thanks,

ravi

|||Then Adamus' example should apply.|||

Hi ,

I think i figured what it was i mean i have like around 200,000 records being pulled in to the extract there ....from which were few records which were nonnumeric in the nchar(3) column i.e apart from '100' , '510' ......there were records with 'MVR' which was causing all the problem... guess...

thanks for the quick responses...

thanks..!!

Ravi

|||

Then check isnumeric before the cast and set non-numerics to 0:

Code Snippet

CREATETABLE #temp(test nchar(3))

INSERTINTO #temp SELECT'111'

INSERTINTO #temp SELECT'AAA'

INSERTINTO #temp SELECT'311'

SELECTCASEISNUMERIC(test)WHEN 1 THENCAST(test asint)ELSE 0 END

FROM #temp

DROPTABLE #temp

Adamus

|||Just a tweak. You may want to set them to NULL, versus a 0. NULLs would be excluded from any math/aggregation totals you may be creating, such as an average. Using a 0 would allow that value to be used. So it's up to you.

In SSIS, you can use a conditional split to move numeric records to your destination table, and non-numerics to the trash, if you desire. Or, you can assign new values to the non-numerics. Up to you.