How to perform a product swap in SSIS

neilsja 141 Reputation points
2021-07-12T16:30:03.567+00:00

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
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,449 questions
{count} votes

Accepted answer
  1. ZoeHui-MSFT 32,411 Reputation points
    2021-07-15T03:33:04.05+00:00

    Hi @neilsja ,

    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


4 additional answers

Sort by: Most helpful
  1. ZoeHui-MSFT 32,411 Reputation points
    2021-07-13T05:41:48.313+00:00

    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]  
    

    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

    0 comments No comments

  2. neilsja 141 Reputation points
    2021-07-14T12:05:36.543+00:00

    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.

    0 comments No comments

  3. Ryan Abbey 1,171 Reputation points
    2021-07-14T20:06:38.15+00:00

    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?

    0 comments No comments

  4. neilsja 141 Reputation points
    2021-07-19T13:06:16.653+00:00

    Thanks guys worked a treat.

    0 comments No comments