Update table if any new data added to list

Question

Post reply

Update table if any new data added to list

anitha.cherukuri

SSCommitted

Points: 1514
More actions
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.

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

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

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

anitha.cherukuri SSCommitted Points: 1514 More actions · Answer 2

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

You said correct.

I am going to implement your soultion in my dataflow.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

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

anitha.cherukuri SSCommitted Points: 1514 More actions · Answer 4

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.

anitha.cherukuri SSCommitted Points: 1514 More actions · Answer 5

anitha.cherukuri

SSCommitted

Points: 1514

September 20, 2009 at 10:51 pm

#1056083

Hi

the problem has been fixed.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 6

anitha.cherukuri (9/20/2009)
Hi
the problem has been fixed.

Let me guess, Converted to the right type..

CEWII

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 7

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 🙂

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 8

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

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 9

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 🙂

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 10

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

CEWII