Importing excel having merged cell in SSIS

Question

My problem is while reading excel file having column cells merged/combined. For example, read below excel data to database.

Excel Input:

+----+-------+---------+-------+
| ID |      NAME       | DEPT  |
+----+-------+---------+-------+
|    | FNAME | LNAME   |       |
+----+-------+---------+-------+
|  1 | Akil  | Tiwari  | IT    |
|  2 | Vinod | Rathore | IT    |
|  3 | Jatin | Khanna  | HR    |
|  4 | Divya | Kherde  | AD    |
|  5 | Amey  | Gauda   | FI    |
+----+-------+---------+-------+

Expected Database Output:

+----+-------+---------+------+
| ID | FNAME |  LNAME  | DEPT |
+----+-------+---------+------+
|  1 | Akil  | Tiwari  | IT   |
|  2 | Vinod | Rathore | IT   |
|  3 | Jatin | Khanna  | HR   |
|  4 | Divya | Kherde  | AD   |
|  5 | Amey  | Gauda   | FI   |
+----+-------+---------+------+

Answer 1

Just connect your excel normally the Excel Connection manager will read it like the following:

+----+-------+---------+-------+
| ID | NAME  | F3      | DEPT  |  <-- Header
+----+-------+---------+-------+
|    | FNAME | LNAME   |       |  <--First Row
+----+-------+---------+-------+
|  1 | Akil  | Tiwari  | IT    |  <-- Second Row
|  2 | Vinod | Rathore | IT    |   .
|  3 | Jatin | Khanna  | HR    |   .
|  4 | Divya | Kherde  | AD    |   .
|  5 | Amey  | Gauda   | FI    |   .
+----+-------+---------+-------+

In the Excel Source Just rename columns like below"

NAME --> FNAME
F3   --> LNAME

Then Just add a Conditional Split that filter rows that have ID = NULL using the following expression

ISNULL([ID]) == false

Then the First row will be ignored