Import excel worksheet with header on row 6

Question

Import excel worksheet with header on row 6

P Jones

SSChampion

Points: 12361
More actions
January 12, 2007 at 7:27 am

#171843

I need to import an excel worksheet (exported from another unchangeable system) where there are some rows (to be discarded) before the header row which is row 6.
I was able to do this in sql 2000 dts but would now like to do the same in sql 2005 SSIS and cannot find out how.
Can anyone help please?
January 15, 2007 at 8:00 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

P Jones SSChampion Points: 12361 More actions · Answer 1

The original poster is still struggling with this. In dts I simply set the first row and last row on the transform data task properties.

But I can't see a SSIS equivalent. HELP PLEASE

Liam Gavin-382521 SSC Veteran Points: 239 More actions · Answer 2

Have you tried using an SQL command (or SQL command from variable) as the data access mode for the Excel source?

You can then set SQL command text as select top 6 from $worksheetname

Creating it from a variable would mean you could also set the worksheet name and rows required dynamically. Variable needs to be created with EvaluateAsExpression = true then use code above as expression "select top " + (DT_WSTR, 10) @[i_rows] + " from " + (DT_WSTR, 10) @[s_ws_name]. Where i_rows and s_ws_name are variables you have created.

Liam.

RafSalas SSC Rookie Points: 36 More actions · Answer 3

I would add a rownumber column to the dataflow pipeline and then use a conditional split to send the rows to the destination where rownumber>6....