The SSIS derived column tends to be a little easier to understand and managing one derived column in an SSIS package could be considered easier. The SQL Query will perform much better than the derived columns but the query could confuse others that may need to maintain this after you. Maintainability and Performance are the two items to consider when making this decision. Which is better, SSIS or SQL? Should you do this in derived columns in your package or should you use SQL statements like the one above to perform this work? In SSIS the derived column transform could be used to do the same conversion that I am doing in the Named Query to get the numeric fields. If you prefer, you can go back to the SSIS package loading this dimension and create these new number columns on the dimension table. You can change the dimension table in the DSV to a Named Query and add these new number fields. You can add these new columns to your dimension and use them as the key to your attributes and order by the key. Your SQL code may need to be adjusted for the number of decimals in your field. You can see that the codes are in the numerical order we wanted. The length of the code is used as the length of the substring, this is too long but since it is the last digit it works fine.Ĭonvert(int,SUBSTRING(code, len(code) – Charindex(‘.’,REVERSE(code)) + 2, len(code))) as Num3 The location of the start of the substring is calculated by subtracting the length of the code minus the location of the last decimal place plus 2. Then we need to get the number after the last decimal. It is the length of the code minus the first decimal location minus the index of the last decimal.Ĭonvert(int,SUBSTRING(code, CHARINDEX(‘.’, code) + 1, LEN(Code) – CHARINDEX(‘.’, code) – Charindex(‘.’,REVERSE(code)))) as Num2, The length of the substring takes a little math. First we get the location of the first decimal plus one to use as the starting point in our substring. This is a little harder as you can tell by the below code. Then we need to get the number after the first decimal but stop at the second decimal. Then is it converting it to an Integer.Ĭonvert(int,SUBSTRING(code,1, CHARINDEX(‘.’, code) – 1)) as Num1, This code it getting the location (CharIndex) of the decimal and then getting the substring up the character before the decimal. To get the first number we simply use a char index to get up to the first decimal with this SQL code. To fix this issue you need get the individual numbers between the decimals in the code as integers and sort on each one. The zero in ten is not even checked because is it alphabetizing when ordering these fields. So the number 10 would come before the number 2. This is happening because a string is evaluated as alphabetical when sorted. You will also notice 2.2.0 should be before 2.10.0. Instead you can see it is lower in the order due to the string order. You want the number 1.1.2 to come before 1.1.10. Notice the numbers are sorted as a string and not numeric. I could not just convert this code to a number and sort on that, because the codes had multiple decimals as seen in this image below. I was working on a Cube in Analysis Services for a client recently and needed to sort on a field that was a varchar but contained numeric data.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |