Skip null rows in SSIS of excel file

Question

I have a SSIS package which is importing multiple Excel file to SQL server but in my excel file there are some null rows on top and it is different in different file for example one excel file has first 5 rows null and data start from 6th row but one of my excel has first 9 rows null and data start from 10th row so is there any method to skip rows according to to null rows dynamically

I tried select * from [sheet$A7:H] but it is working for only one excel file I don't want to write code for every file separately is there any way to do co dynamically

Answer 1

Can you do it dynamically? Maybe, it will depend on the details of your file and how it varies.

As you've noted, you can write a query

select * from [sheet$A7:H]

And if that is in an SSIS Variable, then as long as the query is correct before the Data Flow begins, possibly validates, then you'll be able to skip N rows.

Of course, the devil is in the details in that you would need a preprocessing step, like a Script Task to open the same Excel file and identify the first non-empty row in column A (or whatever column is determined to be of interest)

An alternative approach would involve adding a Conditional Split to your Data Flow.

It could be as simple of a condition as

(IsNULL(Col1))

or maybe

(IsNULL(Col1) || Col1 == "")

• Option 1 Col1 is null
• Option 2 Col1 is NULL or Col1 is the empty string

Whatever your data evaluates to when you bring it into the data flow will tell you what condition you want.

Route those rows to nothing. Leave the Default path out of the Conditional Split to the rest of your Data Flow and the data flow will read all the rows from the worksheet. Only the rows matching our criteria will flow to the rest of downstream components.