"Merging" two Excel files into one output

Question

Hi,

I'm having an Excel file with three sheets where I need to apply the two columns from one sheet, to all the rows from the two other sheets.

I have created a task that reads the first sheet which hold a colum with the CompanyName and one with Currency code. I have then set up a different task to read the second sheet that has all the postings and here I need to apply the CompanyName and Currency Code that I have read from the first sheet.
I simply can't figure out how to do this? I have tried with e.g. Union, Merge and Merge Join but when I insert the data into my table, I end up with a single record with just my CompanyNAme and Currency columns and then after that all the records from the positions sheet without the CompanyNAme and Currency Code.

In T-SQL I'd do it with CROSS APPLY, but I simply can't figure out how to achieve the same in SSIS.

I hope some of you can come up with some ideas? It could very well be really simple but I just can't get my head around it.

Regards
Steen

Answer 1

Hi anonymous userSchlterPersson-6689.

I did some test locally, not sure if you want the result like below.

I used the sort transformation to sort the data and then used the merge join.

The merge join I chose is full outer join you may change with your own needs.

Here is a blog I referred, hope it could give you some ideas.

https://oakdome.com/programming/SSIS_MergeJoin.php

Regards，

Zoe

---

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

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October

Answer 2

Hi Zoe,

Thanks for the feedback.
I have actually tried something like this, but my problem is that I don't have any columns to JOIN on (I'm sorry I didn't mentioned that in my original post).
I just want to apply the Company Name and Currency code to all the rows in the positions table like a CROSS APPLY but I can't see I can make that work with a Merge Join?

But now then I'm thinking about, I could maybe get around it by adding a "CompanyID" column with e.g. a value of 1 in both tables. Then I can use that column to join on and then just don't load that column down to my table.

I'll give that a try and see if that will do the trick :-).

Regards
Steen