question

neilsja-9416 avatar image
0 Votes"
neilsja-9416 asked neilsja-9416 answered

How to perform a product swap in SSIS

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.

sql-server-integration-services
· 1
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.

They are two flat files (i.e. no database)?

A merge join with a left outer join type (the Master File being the left side). You will have to sort both sides on the join fields first (unless you know they are and always will be pre-sorted in which case you can mark them as sorted in the Advanced Editor definition)

0 Votes 0 ·
ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered ZoeHui-MSFT commented

Hi @neilsja-9416,

I did some test for you.

Here is my datasource sample for your reference.

114872-untitled.png

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

114798-screenshot-2021-07-15-112907.jpg

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



· 1
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 @neilsja-9416, may I know how's the issue going on now? If you have any updates, don't forget to tell us.

0 Votes 0 ·
ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered ZoeHui-MSFT edited

Hi @neilsja-9416,

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]

113980-screenshot-2021-07-13-135310.jpg

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



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.

neilsja-9416 avatar image
0 Votes"
neilsja-9416 answered neilsja-9416 published

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.

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.

RyanAbbey-0701 avatar image
0 Votes"
RyanAbbey-0701 answered

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?

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.

neilsja-9416 avatar image
0 Votes"
neilsja-9416 answered

Thanks guys worked a treat.

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.