SSIS Dataflow From Excel Empty Rows

Question

Hi All,

I am using SSIS Dataflow to import data into SQL2008.

My data source is an excel file.

The dataflow is working, however it seems that it is importing empty rows from the Excel file. I don't understand why this is happening.

For example i have data in rows 1 to rows 100,000. But when the data flow task runs it might say it is importing 200,000 rows.

When I then import the data back into excel, I get 200,000 rows of data with 100,000 empty rows in between the data.

Can someone please help?

Thanks

Answer 1

Well Excel is notoriously bad for leaving empty rows anywhere that rows used to exist but were deleted. Maybe this is your problem.

You might consider a conditional split task in the data flow that throws away any rows where the columns are empty