I've got a couple of work flows pulling data from different tables into one table. Some of the data I am pulling is coming in as a decimal number i.e. 3.7, 4.5, some of it is coming in as whole numbers, for the decimal some of the numbers are 0.0.
The destination table column is set to varchar as there are different data types coming from 3 different tables, so just convert all to string.
When I pull the any (0.0) data into the destination table it removes the leading zero to show .0
I know you can do transformations or sql wizardry at the query level but it seems to be once the data is put in the table a leading zero is removed. I understand this is because the field is a varchar and removes the leading zero
How can I reinsert the leading zero if it is value .0 to change it back to 0.0?
the varchar won't remove the leading zeroes. Somewhere in the process, it is being converted probably implicitly. What are the data types of the columns that are being combined?
The data type of the problematic field are decimal(3,1) at source. Trying to put it into a varchar at destination gave me 0.0, but wanted to put just 0 without any decimal points anywhere. Trying to use derived column but was only getting (point zero) .0
I wouldn't use varchar for this - I would use the largest numeric value coming from all 3 systems and convert the smaller to the larger. So, for example - you have an integer coming from system 1 it would be converted to decimal(3,1).
Or - use sql_variant as the data type and just pass the values directly, but that will have other issues downstream.
Or - use separate columns.
If the value is the same value from all 3 systems - then it should be stored in a common type. If it isn't the same from each system then it would need to be in different columns.
I'll rephrase what I said in original post, all the data coming into the ssis the source are decimal, no whole numbers coming in. But only data that is 0.0 I want to convert into 0. I have set the destination column to decimal (3,1) so to convert the 0.0 value when it encounters it, do I need to do a derived column in SSIS?
At present the value being put in is 0.0 to the destination.
If the destination column is defined as a decimal - the value for 0 will be entered as 0.0. That is what it should be defined as - I am not sure why you would want it to be any different.
You are making this much harder than it needs to be - again, set the destination to the largest numeric data type. There is absolutely no difference between 0.0 and 0 or 0.000000 - but there is a huge difference between a varchar and a numeric.