There are times we may have to work with Excel files in SSIS that has subtotals and grand total. We often may have to remove those rows before importing them to the desired database table. We could achieve that without writing a complicated scripts.
Let us see how we could do that in action:
Let us assume we have an Excel spreadsheet that has subtotals and a grand total.
Step 1: Let us go to SSIS and create an Excel connection to our file. Then being in the data flow drag and drop an Excel source and a Union All task and connect them as shown in the image blow. (It is common to us Union All as garbage destination in the development so that we are able to work without having to create a destination for the data.)
Step 2: Open Excel Data Source and change the Data access mode to SQL command. In order to filter the subtotal add any of the columns that doesn’t participate in the subtotal (in our example all columns except Region and Total) and in the where clause specify the selected column <> null.
Finally if you turn on your data viewer you can see that the subtotal and grand total rows are filtered.
Special thanks go to Debra Dalgleish (http://www.contextures.com) for allowing me use their sample Excel data for this blog.
