I am trying to understand using SSIS how I can merge a 'sales order header' table and 'sales order transaction' into a 3rd database table.
I need to create a 3rd table with the 'order number', 'invoice date' and 'customer' fields then on the same row have the transaction entrys.
The reason I need to do this is I then also need to run the new table against the customer table to find what currency they are using and read the conversion rate from a currency table based on the currency and year then write the currency
code into the 3rd table that was created and run a calculation on the values in the row to match that currency rate to ensure each transaction of each order for each customer is calculated from the correct currency back into a single currency for all records.
My theory was to read the header table and establish how many records were in there then do the same for the transaction table. Then build a nested loop on the two values to ensure that as i read a header record it scans the transaction table
for all transactions for that order number and write the data to a new table then moves on to the next order header and repeats the process until all header records are read. This is the part I am stuck on building this correctly in SSIS. I have looked
for some examples or pointers
View Complete Post