I have to do this twice in my SSIS package and I hate long confusing nested if statements. I have to map text strings to a numeric value: if 1 then "Apple" else if 2 then "Orange" These mappings are not in the database (that would make my life too easy) so I have to do it programatically. Other than using the derived column transform and a long nested if statement ( «boolean_expression» ? «when_true» : «when_false») is there any way to do this?

I think the trick is in identifying what makes you tell 1 is Apple and 2 is Orange in first place. In addressing this smartly you might ending up in having an optimized way. You can also move this logic to source query by having case statement or also you can think of having the logic in Script task....but whatever you use the mapping would still be there....so we might as well think whether we can avoid this mapping itself vinu

As Vinu said, you can use case statements in the source query itself but if the source is some text file or excel workbook then you can create a lookup table with all the mappings to make the things easier. You can also use a script component or derived column to achieve this but if any change in the mapping (for 1 you need Banana instead of Apple) is required or some more mapping has to be added then you need to go and make the necessary changes in all the packages. So, I would prefer using a lookup table to achieve this.Nitesh Rai- Please mark the post as answered if it answers your question

It seems that case statements do not work in the query which is very strange. I'll have to look up the syntax. I had a script component but because I am doing it actually 3 times (not 2 as I previously mentioned) this is a sub optimal solution for the exact reason Nitesh mentioned. The first case is the simple: if 1 then 'Apple' if 2 then 'Orange' The other two cases are slightly more complicated because they are being converted from registers if scale1 then num if scale10 then num*10

It seems that case statements do not work in the query. Why?? and how about having a lookup table?Nitesh Rai- Please mark the post as answered if it answers your question

In such cases why dont you move this logic to a view and then use the view to get the required result and use it however you wantvinu

I like Vinu's answer on this one - a view is a very elegant alternative for encapsulating this behaviour... IF the source database "should" know that 1 is "Apple" and 2 is "Orange". But if you're imposing those values externally, and the source system doesn't really understand those values that way, then using a lookup table/file is your best alternative. In my DW environment, I've set up a database devoted exclusively to storing this "metadata" that the source system "knows" - perhaps only in the source code of the system. I use lookups extensively to convert these codes to meaningful values using that metadata. Talk to me now on

Thanks guys for your help, I finally decided on a script transform. Using a view is a good suggestion and it would would work in any other case except mine. Basically this ssis package is for data importing. The "Database" I am querying from is actually an .mdb file that I get from a third party.