is there a way to do a mass update in SSIS

  • AlphaTangoWhiskey

    SSChampion

    Points: 10788

    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

  • Alvin Ramard

    SSC-Forever

    Points: 41190

    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]

  • AlphaTangoWhiskey

    SSChampion

    Points: 10788

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

  • bc_

    Hall of Fame

    Points: 3098

    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]

  • AlphaTangoWhiskey

    SSChampion

    Points: 10788

    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.

  • Jeff Moden

    SSC Guru

    Points: 994867

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • AlphaTangoWhiskey

    SSChampion

    Points: 10788

    Jeff,

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

    Thanks - Tom

  • Jeff Moden

    SSC Guru

    Points: 994867

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • jpalmer-755805

    SSC Eights!

    Points: 959

    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.

  • AlphaTangoWhiskey

    SSChampion

    Points: 10788

    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.

  • jhgotla

    Grasshopper

    Points: 12

    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.

  • Phil Parkin

    SSC Guru

    Points: 243772

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    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

  • Phil Parkin

    SSC Guru

    Points: 243772

    1. This is a 2-year old thread.

    Damn, missed that!

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Jeff Moden

    SSC Guru

    Points: 994867

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

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

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