How to perform a product swap in SSIS

Question

Hi

I have two CSV files to import:

Product Swap:
DEL POINT Prod Code Swap Code
1 TEST1 TEST2
2 TEST3 TEST4

Master File:
DEL POINT Prod Code Qty
1 TEST1 2
2 TEST3 5
3 TEST5 10

What I am trying to do is output the Master file, but with the product code swapped based on a combination of delivery point and product code.

So if (Master File.Prod Code & Master.Del Point) equals any record in the 'product swap' file (if which there will only be one entry if exists), then swap the product code.

So in the example above, the outputted single file will show:

DEL POINT Prod Code Qty
1 TEST2 2
2 TEST4 5
3 TEST5 10

Any help much appreciated.

Answer 1

Hi @neilsja ,

I did some test for you.

Here is my datasource sample for your reference.

You may use merge join to add a new column like shown below.

After left outer join, you will get a column named as new code which have mapped the DEL Point.

Remember to add a Derived Column with the expression: ISNULL([New code]) ? [DEL POINT] : [New code]

And then you will get the column you want as you mentioned, you could transform the data to a file you want.

Here is a detailed SSIS Left Outer Join tutorial for your reference.

https://www.tutorialgateway.org/left-outer-join-in-ssis/

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 @neilsja ,

You may also load the data to SQL database table via SSIS and use a simple command to meet your requirement.

I transformed the two csv as table Product and Master.

update a  
set a.[Prod Code]=b.[Swap Code]  
from [Master] a  
inner join Product b on a.[DEL POINT]=b.[DEL POINT] and a.[Prod Code]=b.[Prod Code]  

And then you may transform the table as you wanted format via SSIS.
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 3

Hi Guys

Thanks for the response, I do not think I was clear enough.

I have a SQL Order table with all the details I need to start with:

G1096,BOOKE,2021-05-26 00:00:00,1.0000,AAA8Z
G1092,BOOKE,2021-05-26 00:00:00,2.0000,AAA8Z
CO1252,BOOKE,2021-05-26 00:00:00,1.0000,AAA8Z
G2042,BOOKE,2021-05-26 00:00:00,2.0000,AAA8Z
G2044,BOOKE,2021-05-26 00:00:00,1.0000,AAA8Z
G2040,BOOKE,2021-05-26 00:00:00,1.0000,AAA8Z
G1073,BOOKE,2021-05-26 00:00:00,1.0000,AAA8Z
G1294,BOOKE,2021-05-26 00:00:00,2.0000,AAA8Z
G1096,BOOKE,2021-05-26 00:00:00,1.0000,AAA2Z

I then will then have a .csv file that will be used to swap certain products:

AAA8Z,G1096,TEST1
AAA8Z,G1092,TEST2
AAA8Z,G1073,TEST3

Then based on this, a file will output with this information:

TEST1,BOOKE,2021-05-26 00:00:00,1.0000,AAA8Z
TEST2,BOOKE,2021-05-26 00:00:00,2.0000,AAA8Z
CO1252,BOOKE,2021-05-26 00:00:00,1.0000,AAA8Z
G2042,BOOKE,2021-05-26 00:00:00,2.0000,AAA8Z
G2044,BOOKE,2021-05-26 00:00:00,1.0000,AAA8Z
G2040,BOOKE,2021-05-26 00:00:00,1.0000,AAA8Z
TEST3,BOOKE,2021-05-26 00:00:00,1.0000,AAA8Z
G1294,BOOKE,2021-05-26 00:00:00,2.0000,AAA8Z
G1096,BOOKE,2021-05-26 00:00:00,1.0000,AAA2Z

As you can see, only three of the entries change to the 'swapped' product code, where delivery point and product code match.

In SSIS I currently have the OLEDB and FlatFile sources in place (within the data flow), and then I am stuck.

BTW, very new to this so really appreciate your help.

Answer 4

Have your SQL table as your "left", use merge join to the csv as a left outer

Where is the output to? Or are you wanting to save this back to the Order table?

Answer 5

Thanks guys worked a treat.