How to split a table into Master/Detail, with generating id SSIS?

I stored a flat file into a table.

The tag P is for the master The tag I is for the detail

Always a P row was precede with a (n) numbers of I rows.

The problem is, The I rows doesn’t have an ID to join the P row.

Need to generate an ID to join the P row with the I rows.

The P row have a two fields that can be helpful. The field “SequenceNumber” store the sequential ID. The field “NumberOfItems” store how many I rows belong to a P Row.

I Need to split the data in two tables master/detail with identifiying ID. I attach an image with the data

Thanks So Much

Original Table
RecordType SequenceNumber NumberOfItems TicketHeaderKey UnitID
P          1              3             ;               1
I          19900          0             FA              19900
I          3000           0             BK              3000
I          0              0             BK              0
P          2              1             ;               1
I          19900          0             FA              19900
P          3              2             ;               1
I          19900          0             FA              19900
I          3000           0             BK              3000

Need Split into two tables some like this

Master Table
RecordType SequenceNumber NumberOfItems TicketHeaderKey UnitID
P          1              3             ;               1
P          2              1             ;               1
P          3              2             ;               1

Detail Table
RecordType SequenceNumber idMasterTable TicketHeaderKey UnitID
I          19900          1             FA              19900
I          3000           1             BK              3000
I          0              1             BK              0
I          19900          2             FA              19900
I          19900          3             FA              19900
I          3000           3             BK              3000

SQL TABLE TO SPLIT

Answer

My primary concern is that you do not have a proper row sequence in the original table. Without this, this is no way to GTD the ordered data set.

In anticipation of you correcting this, I added a column called RowSeq

Example or dbFiddle

Declare @OriginalTable Table ([RowSeq] int,[RecordType] varchar(50),[SequenceNumber] int,[NumberOfItems] int,[TicketHeaderKey] varchar(50),[UnitID] varchar(50))
Insert Into @OriginalTable Values 
 (1,'P',1,3,';',1)
,(2,'I',19900,0,'FA',19900)
,(3,'I',3000,0,'BK',3000)
,(4,'I',0,0,'BK',0)
,(5,'P',2,1,';',1)
,(6,'I',19900,0,'FA',19900)
,(7,'P',3,2,';',1)
,(8,'I',19900,0,'FA',19900)
,(9,'I',3000,0,'BK',3000)


Select RecordType
      ,SequenceNumber
      ,NumberOfItems
      ,TicketHeaderKey
      ,UnitID
 From  @OriginalTable
 Where RecordType='P'
 Order By [RowSeq]


Select RecordType
      ,SequenceNumber
      ,IDMasterTable 
      ,TicketHeaderKey
      ,UnitID
 From ( 
        Select *
              ,IDMasterTable = max(case when RecordType='P' then SequenceNumber end ) over (Order By RowSeq)
         From  @OriginalTable
      ) A      
 Where RecordType='I'
 Order By [RowSeq]

Returns