title: SSIS Package that removes or ignores multiple rows in flat file
body: <p>I'm learning how to develop SSIS packages for ETL systems this week. One of my first objectives is to discover different ways to import flat files into a database. As this is pretty straight forward for the most part, I've been playing around with different flat files that contain a variety of data.</p>
  
  <p>One issue I ran into today was with a Excel document that contained data in the first row, the header information in the second row and foot information in the last couple of rows. What I want to import into the database is the header and all the rows leading up to the footer. I do not want the first row and I do not want the footer.</p>
  
  <p>My current solution is to create a Data Flow task in Advance Settings and OpenRowSet with "Sheet1$A2:I20000". This allows me to open the sheet I want, select the second row (where my header resides) and then select all other rows that are between A2 and I20000. </p>
  
  <p><img src="https://i.stack.imgur.com/ecOF2.png" alt="enter image description here"></p>
  
  <p>This solution also allows me to read the header information (which I want) and all the rows that follow for importation. Unfortunately, this also selects the footer rows and doesn't seem optimize for good performance as the package has to scan a massive range of rows regardless if there is data in those rows or not. </p>
  
  <p>The screenshot below contains the Excel sheet that I'm trying to import based on the MS SQL sample database. The rows I want to remove or ignore are circles with the red box. Everything else not circled is what I want to import.</p>
  
  <p><img src="https://i.stack.imgur.com/RtctI.png" alt=""></p>
  
  <p>Any thoughts on how I can ignore the first row, read the second row for my header information, read the rows that follow the header for my data set and then ignore the last couple of rows that I'm deeming as the footer? </p>
  
  <p><strong>Addition Information About This File</strong></p>
  
  <ul>
  <li>The first row will never change.</li>
  <li>The header row will never change.</li>
  <li>The data set after the header will change values, not data types.</li>
  <li>The first column of footer will never change.</li>
  <li>The second column of footer will change values, not data types.</li>
  <li>The rest of the footer columns will never change.</li>
  </ul>