Post reply

Skip last row EXCEL ssis Conditional Split

  • May 5, 2017 at 7:09 am

    #330211

    hi

    Hi

    I am using visual studio to write an ssis package Visual studio 2010

    it imports and excel sheet of data into a sql server 2012 - to an insisting table (CateringMain) in the sever database

    works great

    however I have another issue
    I need to skip the last row of data of the excel sheet (it contains total values)

    I have written this within a conditional Split

    ISNULL(ID) || ID != "Total:"

    its skipping it, but not importing the other rows of data

    please help

  • May 5, 2017 at 7:20 am

    #1941285

    You're using an Excel Source in your Data Flow task, right?  Why not choose SQL Command for your Data access mode, and then you can write a query that will exclude the row you don't want?  Either that, or import the whole lot into a staging table, clean up, then import from there into your destination table.

    John

  • May 5, 2017 at 7:54 am

    #1941306

    joanna.seldon - Friday, May 5, 2017 7:09 AM

    hi

    Hi

    I am using visual studio to write an ssis package Visual studio 2010

    it imports and excel sheet of data into a sql server 2012 - to an insisting table (CateringMain) in the sever database

    works great

    however I have another issue
    I need to skip the last row of data of the excel sheet (it contains total values)

    I have written this within a conditional Split

    ISNULL(ID) || ID != "Total:"

    its skipping it, but not importing the other rows of data

    please help

    You want to select rows where ID is not null and not Total:?

    !ISNULL(ID) && ID != "Total:"

    PS, what is an 'insisting' table?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • May 5, 2017 at 8:59 am

    #1941330

    Hi

    it was to say existing table sorry lol

    yes I need to select rows where ID is not null and not Total,

    I have used your code and its still only bringing back these two rows

    please help

  • May 5, 2017 at 9:10 am

    #1941331

    joanna.seldon - Friday, May 5, 2017 8:59 AM

    Hi

    it was to say existing table sorry lol

    yes I need to select rows where ID is not null and not Total,

    I have used your code and its still only bringing back these two rows

    please help

    It's a bit difficult to troubleshoot this further without seeing your data. Which values of ID are being selected by the above code? Can you provide an example of an ID which is not being selected by the code?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • May 5, 2017 at 9:18 am

    #1941337

    hi

    example, sorry I am new to SSIS  

    ID           Name    Surname  Dept     Sales
    AA1B2   Jack       Jones      Cakes   £220
    AB1B8   James    Jones      Meat     £1100
    Total:                                                 £1320

    Its the last line of code I am trying to exclude is the line row that says Total

    Please help

  • May 5, 2017 at 9:26 am

    #1941338

    Is the last line always Total? if so then just why note use an a conditional split with the expression:
    [ID] <> "Total:"

    Assuming that the total value is in the second cell.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • May 8, 2017 at 5:27 am

    #1941554

    brill x, thanks again team

Viewing 8 posts - 1 through 7 (of 7 total)

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