Post reply

Data Flow Destinations - Insert Only?

  • April 14, 2008 at 12:29 pm

    #121136

    Question:

    If I do, for example, a Data Flow task where I have an OLE DB Source, a Script Component task, and an OLE DB Destination, am I ONLY allowed to do INSERTS into the destination in that fashion, and not updates or anything else?

  • April 14, 2008 at 12:52 pm

    #802228

    Technically, the OLEDB Destination supports any T-SQL statement by using the Advanced Editor. It does not work well though. If you manage to get this to work, it will update a single row at a time.

    If you want to do an update or run a procedure, the OLEDB Command object (in the transformation section of the toolbox) is far better suited and easier to use. It is still a one-row-at-a-time update, but that is what SSIS is designed for and it often manages a cursor-style update pretty well. Although it is a transformation component, you do not need to use the output. If you really do not like to leave the data flow arrow hanging (like me), you can delete the output or download the Transh Destination Adaptor from http://www.sqlbi.com.

    Interestingly, I have found I could improve OLEDB Command update performance using a conditional split and multiple updates. It makes sense that running parallel updates could improve performance, but until I tried it I was not really confident that it would really work correctly.

  • April 14, 2008 at 5:11 pm

    #802311

    I think i see what you're talking about - the AccessMode property.

    Thanks for the tip - I'll read up on the conditional splitting you referred to.

Viewing 3 posts - 1 through 2 (of 2 total)

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