How to get the previousID in SSIS using Lookup?

Question

i have two input tables:

1) Site:

 site_id||   site_name||   site_location


 1000   ||    abc     ||    XYZ_123

 1001   ||    tyu     ||    ERD_123

 1002   ||    iok     ||    FTR_678

 1003   ||    okn     ||    YHU_987

 1004   ||    ybg     ||    OLP_008

 1005   ||    qwe     ||    PLM_126

2)

product:

 Product_id|| product_name||start_date||end_date

   212     || sme1        ||2014-12-25||2017-03-13

   250     || try1        ||2013-12-15|| 2017-03-13

   267     || inu1        || 2015-03-27|| 2017-03-17

I need to check how many times the id is repeated and order it like the output table.

this is my output table structure :

Id|| site_id|| product_id|| previous_id|| start_date|| end_date

1 || 1000   ||250        || null       || 2015-01-01||2017-03-13

2 || 1001   ||250        || 1          || 2014-12-25||2015-01-01

3 || 1002   ||250        || 2          || 2013-12-15||2014-12-25

4 || 1003   ||267        || null       || 2015-03-27|| 2017-03-17

5 ||1004    ||212        || null       || 2016-01-01||2017-03-13

6 || 1005   ||212        || 1          || 2014-12-25||2015-12-30

i have added a new column using dervied column for PreviousID but to map the previous PreviousID with the ID column i am unable to figure out a way. I used the lookup transformation to lookup the values of Site_id and product_id but i am making some mistake and i am unable to figure out that mistake.

i am using visual studio 2010.

any help is appreciated.

Is there some reason you have to do this in SSIS? I would do this in a SQL stored procedure. To do this in SSIS, I think you would have to use a script transformation. — Tab Alleman, Mar 13 '17 at 13:37

score 2 · Accepted Answer · answered Mar 14 '17 at 13:59

You can do this in a script transformation. First you would create a package variable to hold the value of the PreviousID. As the script transformation handles each row, you set the value of the PreviousID variable at the end of the script, and read the value of the variable at the beginning of the script to get the value that was set by the previous row. You can also apply whatever logic you are using to determine when to reset the PreviousID to a null. It's not clear from your example what that logic is.

How to get the previousID in SSIS using Lookup?

1 Answers1