Post reply

Update table if any new data added to list

  • September 17, 2009 at 9:41 pm

    #226301

    Hi friends

    I am new to SSIS,I would like to know some of the things.

    I Loaded data from excel into Sql Server Table.

    So what i want to know is if any data added to excel list ,my table has to be updated automaticallly.

    And it should idplay old data along with new data.

    Can anybody help me ?

    Thanks.

  • September 17, 2009 at 10:13 pm

    #1055365

    Lets see if I understand..

    You have data in excel that periodically changes. Once it changes you want automatic updates to the table.

    You want to load it into a table, if the data exists update it, otherwise insert it.

    Right?

    First issue is that the table will not be automatically update, you will have to schedule the job. Also keep in mind the package will fail if the file is open in excel on ANY computer.

    Second issue, the excel spreadsheet must contain some identifier so that it can decide that a record has changed as opposed to a new record.

    Third, provided you can tell inserts/updates you can use a lookup transform in the dataflow to look at the tabel in the database, for records it finds it does an update usinf an OLEDB Command transform, for records it doesn't you will have to configure the error output on the lookup transform, in that output will be records that no match was found, you can feed that into an OLEDB Destination.

    Solved?

    CEWII

  • September 17, 2009 at 10:30 pm

    #1055371

    Thanks Elliot..........

    You said correct.

    I am going to implement your soultion in my dataflow.

  • September 17, 2009 at 10:34 pm

    #1055375

    Good to hear, I have implemented this solution many times.. In SSIS 2008 it is slightly easier, because the lookup in 2008 has a failed lookup path instead of using the failed row path..

    CEWII

  • September 20, 2009 at 9:11 pm

    #1056082

    HI Elliot

    I am getting some error when i am trying to use look up table

    In put column has a datatype which cannot be joined in.

  • September 20, 2009 at 10:51 pm

    #1056083

    Hi

    the problem has been fixed.

  • September 21, 2009 at 8:30 am

    #1056257

    anitha.cherukuri (9/20/2009)

    Hi

    the problem has been fixed.

    Let me guess, Converted to the right type..

    CEWII

  • September 22, 2009 at 9:46 am

    #1056903

    Second issue, the excel spreadsheet must contain some identifier so that it can decide that a record has changed as opposed to a new record.

    Hi,

    Can you elaborate or give some example on this ? :w00t:

    Thanks,

    \\K

    ______________________________________________________________________________________________________________________________________________________________________________________
    HTH !
    Kin
    MCTS : 2005, 2008
    Active SQL Server Community Contributor 🙂

  • September 22, 2009 at 11:26 am

    #1056992

    SQL_Quest-825434 (9/22/2009)

    Second issue, the excel spreadsheet must contain some identifier so that it can decide that a record has changed as opposed to a new record.

    Hi,

    Can you elaborate or give some example on this ? :w00t:

    Thanks,

    \\K

    What I'm saying is that you need some value that is unique to a particular row that will NOT change. The reason is that without that how are we going to detect we have an update instead of an insert? We need some key value that we can look at the data already in the table and say "hey, I already have that one, so I can just do an update", otherwise it is an insert. Without such a value you are basically guessing based on a bunch of fields that "kind of" look like they are the same, that method, is nothing shy of a disaster and cannot guarantee any level of data purity.

    Clear?

    CEWII

  • September 22, 2009 at 8:06 pm

    #1057248

    Elliott W (9/22/2009)

    SQL_Quest-825434 (9/22/2009)

    Second issue, the excel spreadsheet must contain some identifier so that it can decide that a record has changed as opposed to a new record.

    Hi,

    Can you elaborate or give some example on this ? :w00t:

    Thanks,

    \\K

    What I'm saying is that you need some value that is unique to a particular row that will NOT change. The reason is that without that how are we going to detect we have an update instead of an insert? We need some key value that we can look at the data already in the table and say "hey, I already have that one, so I can just do an update", otherwise it is an insert. Without such a value you are basically guessing based on a bunch of fields that "kind of" look like they are the same, that method, is nothing shy of a disaster and cannot guarantee any level of data purity.

    Clear?

    CEWII

    you have described based on primary key value of a particular row. The reason I asked is to clarify what you really mean by "some identifier ".

    In my environment, the data came was really dirty meaning the users were sending the spreadsheets by appending the new contents every time and if there was any change in the previous record, they made it as a new record with just one field (column) updated and rest columns unchanged. this resulted in duplicates !

    Thanks for the clarification 🙂

    \\K

    ______________________________________________________________________________________________________________________________________________________________________________________
    HTH !
    Kin
    MCTS : 2005, 2008
    Active SQL Server Community Contributor 🙂

  • September 22, 2009 at 9:12 pm

    #1057261

    Sorry, it came accross as not understanding the entire point I was making. It sounds like you understand..

    CEWII

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

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