Handling Unstructured Data from Excel in SSIS

I need help with Handling Unstructured Data from Excel. The merged cell data needs to be splitted and set to the respective columns in this case. I have attached the source and target.
The Merged Data need to be unmerged and the data need to be populated to those unmerged columns.
In SSIS when i imported this excel it unmerges and places the data in the first column then leaves the other cell as NULL.

Source: tbl_e1 -
Target: tbl_e2
target to be loaded in SSMS.

Detailed Explanation of Requirement:

My Excel Source which has vertically merged cells on Dept1,Dept2 and horizontally merged cells on row2 and row3 on dept2 column as you can see which has the value Banking. similarly row2 and row3 on value column as you can see which has the value 20000. Note that when i imported this source in ssis I can see that this is how it arranges things,

I want to populate the unmerged cells with the data which was inside those cells when it was in merged state (ie. Source).

I want the horizontally merged cells Dept1 and Dept2 of ID 1 to have HR as value in Dept1 and Dept2.

For the Vertically merged cells on Dept2 column for ID 2 and ID 3 I want them to have Banking in Dept2 for Both Id2 and Id 3. Similarly for Column Value which has Vertically Merged cells on ID 2 and ID 3 I want them to have 20000 in Value for Both Id2 and Id 3.

I thought of trying this using the window functions which is not working and other logical things with script task.

You need to clean the file before attempting to do anything with it in SSIS, it's much simpler to fix it first.

The following Excel VBA sub will find and replace all merged cells by unmerging them and then replacing them with the same value as the source cell.

Sub UnmergeCellsAndDuplicateValues()
    
    Dim ws As Worksheet
    Dim r As Range
    Dim RangeAddress As String
    Dim CellValue

    For Each ws In Worksheets
        ws.Activate
        For Each r In ws.UsedRange
            If r.MergeCells Then
               RangeAddress = r.MergeArea.Address
               CellValue = r
               r.MergeCells = False
               Range(RangeAddress) = CellValue
            End If
        Next
    Next ws
    
End Sub

You could probably build a small executable that takes a filename as a parameter, opens the workbook and runs this VBA over it but I've not done that as I rarely use it. However, that would mean you could use this in your SSIS workflow , I suppose it depends on how often this will need to be run and/or will you need to run batches of files etc...