August 14, 2009 at 6:12 pm
I'm trying to convert number values like 0 , 1, 2 which are stored as char(10) datatype to numeric(1,0) datatype.
Both source and destination's are SQL Server.
Tried various type casts but no luck.
Any help is appreciated.
Thank you.
August 14, 2009 at 10:31 pm
harry.sh16 (8/14/2009)
I'm trying to convert number values like 0 , 1, 2 which are stored as char(10) datatype to numeric(1,0) datatype.Both source and destination's are SQL Server.
Tried various type casts but no luck.
Any help is appreciated.
Thank you.
Please provide us with the DDL of the table, sample data, and what you have tried so far. For assistance in meeting this request, please read and follow the instructions in the first article referenced in my signature block regarding asking for assistance.
August 14, 2009 at 11:37 pm
The source table structure is somewhat like this
Create table call_fact(callfactid int, track_num bigint, optiondesc varchar(20),
tot_hold_time int , tot_talk_time int, distribution_type char(10),contract_id varchar(10))
Values for columns are like this
(13456723, 34567892, 345, 34, 0, 45678)
(231468890, 17923450, 5, 310, 234, 12312)
I need to convert distribution_type from char(10) to numeric(1,0) in the destination.
distribution_type has variable values from length 1 to 10 in the source.
I have tried conversion from char(10) to WSTR and then to numeric
also from char(10) to WSTR to DT_R8
Please suggest proper transformations.
Thank you
August 15, 2009 at 2:15 am
You could use CAST to set the datatype at source:
declare @distribution_id char(10), @num_dist_id int
set @distribution_id = '1'
set @num_dist_id = cast(@distribution_id as int)
select @distribution_id char10, @num_dist_id numeric
August 15, 2009 at 9:40 pm
After converting from char to int in the source ,do i need to transform int to numeric using SSIS..if so, could you please suggest ssis type cast or data conversion for the same.
Thank you.
August 16, 2009 at 1:46 am
If your source data is numeric, it can remain numeric all the way through the data pipeline without any need for transformations.
You should check the meta data for your source component to ensure that the input fields are of the correct (for your needs) type.
August 16, 2009 at 9:24 pm
As posted earlier my input in source is of Char data type with values like 0,1,5,6, 12,14, 22.
My destination data type is numeric(1,0). Do i need a transformation at all.
As you said earlier to transform char to int. Would i map int to numeric directly??
Can i have a definitive reply or solution please.
Thank you.
August 16, 2009 at 9:33 pm
harry.sh16 (8/16/2009)
As posted earlier my input in source is of Char data type with values like 0,1,5,6, 12,14, 22.My destination data type is numeric(1,0). Do i need a transformation at all.
As you said earlier to transform char to int. Would i map int to numeric directly??
Can i have a definitive reply or solution please.
Thank you.
Heres the problem I see; the value '14', for example, will not convert to a decimal value with the format (1,0). It is too large. So you have a problem right there that you need to resolve before you even worry about how you are going to do the conversion.
August 16, 2009 at 9:48 pm
Thanks for the reply.
Looking for more replies/solutions......
August 16, 2009 at 10:03 pm
harry.sh16 (8/16/2009)
Thanks for the reply.Looking for more replies/solutions......
Not much that can be done until you resolve the problem with the destination decimal(1,0) or tell us what you want done with values greater than 9.
August 17, 2009 at 2:04 am
Harry, I would change the data type from decimal to integer for your destination column. Then in SSIS, use a derived column task to convert your distribution_type to an integer, in the expression column of the Derived Column task put: (DT_I4)[distribution_type].
This will convert the distribution_type field to an integer for you. Lynn is 100% correct, you are not going to get anywhere trying to put a 2 or more digit number into a 1 digit column.
----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
August 17, 2009 at 1:26 pm
My Destination column data type cannot be changed it has to remain numeric but i can make it to numeric(2,0) or numeric (3,0) depending on the source which has max length of 4.
I got my package working finally by converting char to int in the source
and then used DT_NUMERIC(Distribution type) to type cast it .
Only problem was i had to set on truncation to 'ignore failure'.
Let me know if my approach is right.
August 17, 2009 at 9:12 pm
harry.sh16 (8/17/2009)
My Destination column data type cannot be changed it has to remain numeric but i can make it to numeric(2,0) or numeric (3,0) depending on the source which has max length of 4.I got my package working finally by converting char to int in the source
and then used DT_NUMERIC(Distribution type) to type cast it .
Only problem was i had to set on truncation to 'ignore failure'.
Let me know if my approach is right.
You need to redefine your numeric field large enough to hold the largest value from the source. This is why you had to set truncation to 'ignore failure'. You have data that won't fit into your current column with a definition of numeric(1,0).
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply