Post reply

is there a way to do a mass update in SSIS

  • March 10, 2009 at 10:31 am

    #128551

    Without using a stored procedure is there a way to do a mass update in SSIS?

    Currently using the OLE command, which works OK but only with a small number of rows.

    thanks - Tom

  • March 10, 2009 at 10:38 am

    #956938

    What do you mean by "mass update"?


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • March 10, 2009 at 10:48 am

    #956943

    Sorry,

    I have a staging table and the production table. I use a conditional split after a merge join (left outer) to determine if the row exists in the destination (or if it is a new row)

    if existing row then I am using the OLE command to update the production table row by row 🙁

    in a sproc you can do an update like the following:

    update p

    set p.f3 = s.f3, p.f4 = s.f4 ...

    from Staging s

    left join Production p

    on s.f1 = p.f1 and s.f2 = p.f2

    where p.f1 is not null

    this would update all specified fields in production table with values from staging table.

    a similar query can be used to insert new rows by specifying where p.f1 is null

    I am wondering if there is a ssis component that can accomplish this same type of update. I'm guessing this is stored procedure territory....

  • March 10, 2009 at 1:53 pm

    #957057

    you can use an "Execute SQL Task" in the Control Flow to do sql statements or stored procs. not sure if that fits your scenario. bc

    [font="Arial Narrow"]bc[/font]

  • March 10, 2009 at 3:55 pm

    #957127

    Thanks, That's what I decided to do.

    It would be nice if I could make better use of the error handling, logging, and auditing that we've already buillt into SSIS and do these type of batch operations.

    I guess I want the control of RBAR with the speed of batch processing.

  • March 10, 2009 at 9:27 pm

    #957241

    tvanharp (3/10/2009)

    Thanks, That's what I decided to do.

    It would be nice if I could make better use of the error handling, logging, and auditing that we've already buillt into SSIS and do these type of batch operations.

    I guess I want the control of RBAR with the speed of batch processing.

    Heh... understood on that. Sounds strange, but you don't need error handling if you anticipate all the errors. In other words, add a column to your staging table as an "IsValidated" column and validate all the rows in the staging table marking bad rows with a "0". Then, only insert or update good rows and write the rest to an "action required" table.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 12, 2009 at 5:03 am

    #958180

    Jeff,

    That's a good idea, I could simulate the integrity and referential rules through a query and flag the rows.

    Thanks - Tom

  • March 18, 2009 at 7:31 pm

    #962411

    Thanks for the feedback... and yes, that's precisely what I was talking about. You also don't need to spend a lot of time deleting that way, either (although, like anything else, "It Depends")

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 23, 2009 at 9:08 am

    #964487

    Wouldn't the Slowly Changing Dimension task work for this? I've used the SCD task many times and it automagically creates the update and insert statements based on how you define the task.

  • March 23, 2009 at 9:51 am

    #964547

    yes it does, but it does not do set based updates. It's row by row. Which makes it very slow.

    I do use it for most of my dimensions because of the type II support it offers.

  • March 22, 2011 at 3:54 pm

    #1302210

    You may want to try out the Merge statement. It is quite easy to use. It handles all Insert/Update/Delete depending on whether rows exist in the Staging and/or production tables.

  • March 23, 2011 at 1:33 am

    #1302307

    Tom Van Harpen (3/10/2009)

    Sorry,

    I have a staging table and the production table. I use a conditional split after a merge join (left outer) to determine if the row exists in the destination (or if it is a new row)

    if existing row then I am using the OLE command to update the production table row by row 🙁

    in a sproc you can do an update like the following:

    update p

    set p.f3 = s.f3, p.f4 = s.f4 ...

    from Staging s

    left join Production p

    on s.f1 = p.f1 and s.f2 = p.f2

    where p.f1 is not null

    this would update all specified fields in production table with values from staging table.

    a similar query can be used to insert new rows by specifying where p.f1 is null

    I am wondering if there is a ssis component that can accomplish this same type of update. I'm guessing this is stored procedure territory....

    What you could do is redirect all of the 'found' rows to another staging table in SQL Server and then run your set-based UPDATE after completion of the dataflow in an EXECUTE SQL task (assuming you've cleared down your staging table first, of course:-) )

    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.

  • March 23, 2011 at 4:04 am

    #1302351

    jhgotla (3/22/2011)

    You may want to try out the Merge statement. It is quite easy to use. It handles all Insert/Update/Delete depending on whether rows exist in the Staging and/or production tables.

    1. This is a 2-year old thread.

    2. You don't know if the OP is using SSIS 2005 or not, so the MERGE statement could be no option.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • March 23, 2011 at 4:07 am

    #1302352

    1. This is a 2-year old thread.

    Damn, missed that!

    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.

  • March 23, 2011 at 7:15 am

    #1302438

    Heh... I don't mind 2 year old threads being resurrected especially if someone has a good tip. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 16 total)

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