I have been asked to do the following in ssis. Anyone any ideas how I can go about it. I have the first bit done. its really from the if none of the above 2 met then do the rest part.
If CurrencyCodeSold=SecurityCaption11, populate NotionalField with BaseCurrencySold; if CurrencyCodePurchased=SecurityCaption11, populate Notional with BaseCurrencyPurchased;
IF none of the above 2 scenarios are met do the following:
…...store CurrencyCodePurchased & CurrencyCodeSold as temp variables
…..perform a lookup of the currency in SecurityCaption11 to the XrateNotional file Base field (new)
…..starting with the first currency code in that file, find the first code that is either CurrencyCodePurchased or CurrencyCodeSold
…..if the match is CurrencyCodePurchased, take the rate field and multiply this by the BaseCurrencyPurchased (in 167) amount and populate to NotionalAmount
…..if the match is CurrencyCodeSold, take the rate field and multiply this by the BaseCurrencySold amount and populate to NotionalAmount