question

$$ANON_USER$$ avatar image
0 Votes"
$$ANON_USER$$ asked EchoLiu-msft edited

SSIS -Insert missing records from existing record

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




sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered $$ANON_USER$$ commented

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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @EchoLiu-msft Please see below table structure. Create table Exchange_Rate( Currency_ID int not null, Customer_Currency nvarchar(10) null, Local_Currency nvarchar(10) null, Effective_Date datetime null, Exchange_Rate numeric(11,5) ) on [primary] go I have an SSIS package that pulls the data from a flatfile into the DB. The issue is that the records are missing from flatfile but in the package I have to calculate the missing rates by swapping the Local_Currency/Customer Currency then for the Exchange rate I need to calculate it by 1/existing exchange rate. Please see below the current table results: ![111281-image.png][1] The SISS should produce the following for missing results ![111272-image.png][2] [1]: /answers/storage/attachments/111281-image.png [2]: /answers/storage/attachments/111272-image.png

0 Votes 0 ·

Do you mind using the tsql method? I think tsql will be more convenient to handle.

0 Votes 0 ·

IDon't have much control over the DB and the Client prefers the changes on SSIS, if you don't might you can share t-sql option as well. Can you assist with both SISS method and tsql method

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

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:
112032-image.png

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.



image.png (12.6 KiB)
image.png (14.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.