1

I need to loop through a series of spreadsheets (all in the same folder), pulling data from the same cells within the same named range in each, into an existing SQL database, using SSIS (SQL Server 2008 R2).

I started by using the information in How to loop through Excel files and load them into a database using SSIS package? as a point of reference.

However, because my files don't run in a strict columnar format (i.e. the whole of column C plus the whole of column E, etc.), I am struggling with it.

My sheet is as follows:

Spreadsheet

Basically, the area outlined in red (A6:E11) will be the named range (done this way to allow for additional rows as we move forward) and the yellow cells are those that I need to import.
Let's assume that the range will be named "My_Range"

I need to import a row into the database for each of the rows in the range (currently rows 6 through 11).

e.g.
DBase: Col1, Col2, Col3, Col4
Row 1 = B3....B4....C6....E6
Row 2 = B3....B4....C7....E7
Row 3 = B3....B4....C8....E8
etc..

Any help would be greatly appreciated as I need to find the most efficient way to do this for up to 100 files per night.

If you can help me to get the correct data in the correct format from just 1 file, I can work on the multiple-file problem next.

Thanks guys.

Community
  • 1
  • 1
CraigA
  • 147
  • 1
  • 3
  • 13
  • Have you tried using two different selects, one for cells B3 and B4 and another for the cells in your range, and then using a Merge transform to bring them into a single data flow path? – Brian O''Byrne Jun 17 '13 at 12:52
  • I haven't. How would I do that? – CraigA Jun 17 '13 at 12:59

1 Answers1

3

One of the nifty things you can do with the Excel source in SSIS is define the actual range you want. So instead of saying I want "Sheet1" Put into the Sheet1$A5:E.

Just ignore the columns you don't want.

Something llke this.

enter image description here

EDIT:

You might want to use an excel script source to grab the first 2 rows if they are always in the same spot.

Dayton Brown
  • 1,228
  • 3
  • 16
  • 31
  • 1
    Dayton -- good solution! IN SSIS 2010.. Go to Advanced Editor for Excel Source // Component Properties // Custom Properties //OpenRowset// Sheet1$A5:E – Anoop Verma Jun 17 '13 at 20:08