SSIS -Insert missing records from existing record

Question

I am new to SSIS. have a SSIS package to load the below data from another source to a SQL Table. The data shows the exchange rate values for currency conversion. This rate exists for AUD/INR however it is missing for INR/AUD. I need to calculate the inverse of the missing rate by coping the exiting one and taking the rate 1/exchange rate I used @Reza Raad's thread/exchange rates to explain the change I need to do

Current results:
Customer_Currency Local_Currency Effective_Date Exchange_Rate
AUD INR 2011-03-29 45.85
AUD INR 2011-03-28 45.91
AUD INR 2011-03-26 45.50
AUD INR 2011-03-25 45.39

Expected Results:

Customer_Currency Local_Currency Effective_Date Exchange_Rate
INR AUD 2011-03-29 1/45.85 = 0.022
INR AUD 2011-03-28 1/ 45.91
INR AUD 2011-03-26 1/ 45.50
INR AUD 2011-03-25 1/45.39
AUD INR 2011-03-29 45.85
AUD INR 2011-03-28 45.91
AUD INR 2011-03-26 45.50
AUD INR 2011-03-25 45.39

Answer 1

Please refer to the tsql method:

CREATE TABLE #test(Customer_Currency char(15),Local_Currency char(15),  
Effective_Date date,Exchange_Rate decimal(4,2))  
INSERT INTO #test VALUES('AUD','INR','2011-03-29',45.85),  
                        ('AUD','INR','2011-03-28',45.91),  
 ('AUD','INR','2011-03-26',45.50),  
 ('AUD','INR','2011-03-25',45.39)  
  
SELECT * FROM #test  
  
SELECT Local_Currency as Customer_Currency,Customer_Currency as Local_Currency,  
Effective_Date,CAST(1/Exchange_Rate AS DECIMAL(4,3))  
FROM #test  
UNION ALL  
SELECT * FROM #test  

Output：

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Hi anonymous user,

Is your source also a SQL table? If so,please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …).

If not, please share your source data. For us to test.