Calculating a new value for transformation

[netcashin]

Just started using the SQL 2005 last week and in the process of transferring a database with some minor changes to structure and values using SSIS packages.

I have hit one stumbling block. I have a field in a table that has a range of values between 1 and 1000. If the value is between 1 and 250, I need the value 1 to go into a new field in the new db. And if it is between 251-500, the value 2, 501-750, the value 3 and 751 to 1000, the value 4.

Seems pretty straight forward but I can't find the answer with as I am still sorting through all the terminology with 2005 and finding my way around.

Thanks in advance.

 

[manmaria]

One way of doing is by Derived Column Transformation. Derived Column is your new field and the expression is your case statment on your range field. One example of expression is

SUBSTRING(Time_Period,1,1) == "0" ? Time_Period : "0" + Time_Period

--if 1 column of Time_Period='0' then Time_Period else Concatenate(i.e +) '0' with Time_Period
(I have it ready this expression, so I am giving this example)

HTH

 

[netcashin]

Thanks for the info. I have used the Derived Column Transformation for a couple calculations but if there isn't a mathematical formula to account for all outcomes, it won't work.

I am in need of a true if/then type of transformation. I have a couple of other fields also can't be calculated.

 

[Catadmin]

Have you looked into creating three different conditional tranformations, or 3 Execute SQL Tasks, with a MERGE or MERGE JOIN (Data flow tasks) at the end of them?

I'd do 3 separate Execute SQL Tasks to separate out your 0-250 into one group, the 251-500 into a second group and then the 501-750 into a third group. Then bring the data back in together, but dump that one column into a different field depending on what "group" you're pulling the data from.

Just a thought.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Catadmin]

Oh, oh! Almost forgot the UNION ALL dataflow task. That looks fun, nifty, and perfectly capable of doing what you need it to do. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"