Post reply

Deleting rows before Insertion

  • January 22, 2009 at 4:27 am

    #127485

    Hi All,

    Can you please suggest me how to delete rows from a particular table before inserting rows into that using SSIS.

    Please reply fast.

    Thanks all.

  • January 22, 2009 at 4:29 am

    #930995

    Use SQL command on the first step(before inserting) of SSIS package, then insert into the table 🙂

  • January 22, 2009 at 4:45 am

    #931006

    Can u plz be more specific.

    Its not exactly deleting.

    I need to update the existing row, if the value for the same row comes from the source.

    Please tell me how to do it.

    Thanks.

  • January 22, 2009 at 4:51 am

    #931010

    What you need to do, is add a lookup transformation that will check against the destination table and use this to check whether the value exists already,

    If it does exist then you may need to send this data-flow to an update SQL command, and send all the new values to a database destination.

  • January 22, 2009 at 4:59 am

    #931016

    Can u please be more specific.

    I have used Look up transformation.

    After that, which transformation to use to find the value is already existing.

    As i am a beginner to SSIS, please explain me.

    Thanks.

    🙂

  • January 22, 2009 at 5:07 am

    #931019

    In the look-up transformation.

    select the destination table , and the columns tab join the input columns to the destination table based upon your primary key(s).

    Then add this primary key column from the destination table to the lookup column.

    Attach this look-up transformation to a conditional split, and have the criteria for the split as where you have a primary key from the look-up (as in the record exists)

    send the new ouput flow to a destination transformation.

    send the update output flow to a destination transformation that includes update statements.

  • January 22, 2009 at 5:13 am

    #931021

    please help me how to use conditional split. 🙂

  • January 22, 2009 at 5:23 am

    #931028

    conditional split is fairly easy, attach the input data flow from your look-up transformation.

    open up the properties of the split, in the condition box add the condition ISNULL(YourPKgoesHere). Give this output the name of NewRecord.

    do the same again but use a different condition NOT ISNULL(YourPKgoesHere), call this output

    UpdateRecord

    when you connect up the output of the slpit it will ask what output you want to use, send the NewRecord to the insert destination, send the UpdateRecord to your update destination.

    One thing i forgot to mention about the lookup is that you will need to change the error options to ignore error on lookup failure or else will not get the missing values

  • January 22, 2009 at 5:39 am

    #931040

    Hi,

    But I din't find any NOT IS NULL condition in Conditional split.

    Where it is?

    How to use it?

    Thanks for your replies and ur help. But, I din't come up with the solution.

    So, please help me.

    Thanks.

  • January 22, 2009 at 6:31 am

    #931102

    Search around this site and on google for "Slowly Changing Dimension". The topic you are asking about has been discussed a lot. I have posted a number of examples and depending on the size of your tables, a transformation component has been provided by MS for doing this.

    I would also suggest you visit www.sqlbi.com and take a look at the TableDifference component.

Viewing 10 posts - 1 through 9 (of 9 total)

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