Import excel worksheet with header on row 6

  • P Jones

    SSChampion

    Points: 12323

    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?

  • This was removed by the editor as SPAM

  • P Jones

    SSChampion

    Points: 12323

    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

    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

    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....

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

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