Determine the row of data in a table through SSIS

• pwalter83

  SSChampion

  Points: 14575

  More actions

  June 13, 2013 at 7:52 am

  #273826

  Hi,

  I have a requirement based on 2 parts to determine a row of data in a table through SSIS :

  a. Work out the LEG_SEQ_NBR of the VESSEL, VOYAGE and LEG already stored in table1.

  To do this join table1 and join table2 through BL_ID column.

  b. Work out the 2ndVESSEL, 2ndVOYAGE and 2ndLEG.

  Once we have identified the LEG_SEQ_NBR of the VESSEL,VOYAGE, LEG already stored in table1 we need to add '1' to this value and then find that LEQ_SEQ_NBR in table2.

  The DDL of table1 and table2 along with the test data are as below:

  ------------------------------------------------------------

  CREATE TABLE [dbo].[table1](

  [BL_ID] [decimal](10, 0) NOT NULL,

  [VESSEL] [nvarchar](10) NULL,

  [VOYAGE] [nvarchar](12) NULL,

  [LEG] [nchar](3) NULL,

  [BLNO] [nvarchar](17) NULL

  ) ON [PRIMARY]

  CREATE TABLE [dbo].[table2](

  [BL_ID] [numeric](10, 0) NULL,

  [LEG_SEQ_NBR] [numeric](3, 0) NULL,

  [VESSEL_CD] [varchar](10) NULL,

  [VOYAGE_CD] [varchar](12) NULL,

  [LEG_CD] [char](1) NULL,

  ) ON [PRIMARY]

  INSERT INTO [table1]

  VALUES('1','CEUR','032E','E','21')

  INSERT INTO [table2]

  VALUES('1','1','CEUR','032E','E')

  INSERT INTO [table2]

  VALUES('1','2','MARB','794S','S')

  ---------------------------------------------------------

  For Example:

  BLNO = 21 and BL_ID = 1

  Current Vessel/Voyage/Leg in table1 = CEUR 032E E

  LEG_SEQ_NBR of this Vessel/Voyage/Leg in table1 = '1'

  Therefore if we add '1' to this value we'd be looking for LEG_SEQ_NBR '2' as the 2ndVESSEL,2ndVOYAGE,2ndLEG.

  In this case that would = MARB 794S S

  Does somebody pls know how this can be worked out through an SSIS job ?

  Thanks.