Incremental data

Question

Post reply

Incremental data

Prassad Dabbada V R

Ten Centuries

Points: 1251
More actions
August 25, 2010 at 11:30 pm

#225198

What is the best way to get incremental data daily using SSIS from another SQL Server?
Do we need to use SSIS? or do we have some other technic.

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

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

can u brief about your requirement ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Prassad Dabbada V R Ten Centuries Points: 1251 More actions · Answer 2

yes.. I can brief the question.

I have 2 SQL Servers say S1 and S2.

I have to dump data from S1 to S2 but not all the data. I have to get data which is newly added or updated.

to achive this I want the best to get the data

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

Using SSIS would be better

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

drew.allen SSC Guru Points: 76985 More actions · Answer 4

Depending on the number of tables and the types of updates, replication may actually be your best option. SSIS is going to have a hard time dealing with deletes, for example.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

edward_jeba SSC Veteran Points: 241 More actions · Answer 5

If the source data will not get updated/deleted, then you can use an ID column if there is one or an incrementing datetime column.

We use a query that will get the last ID or Datetime till which we have data. This you can do from the destination table or can have a lookup table.

Then this query will fetch all the data greater than the last value and the Max value in the source.

If you are using a lookup table, you need to insert this value (Max Value) in to the lookup table for getting the incremental data during the next run.

HTH

Edward.

girish.khullar Old Hand Points: 312 More actions · Answer 6

Replication would be a good option!!!! or you may use backups and restore option

Prassad Dabbada V R Ten Centuries Points: 1251 More actions · Answer 7

The problem with Replcation is while replicating data from one server to another, if anything goes wrong or any other disaster like power failure or hardware failure happend then again we have to restart the whole thing, please correct me if I am wrong.

girish.khullar Old Hand Points: 312 More actions · Answer 8

You are very right!!! in case of replication, if a failure occurs you need to get the snapshot of the whole database and then proceed....looking at this scenario, why don't you go for log shipping, here you need to apply the logs only and that too could be done offline...........

AmolNaik SSCarpal Tunnel Points: 4767 More actions · Answer 9

In order to get the incremental data, first of all you need to store the last fetched date in a table and use this date as a basis to get your incremental data.

Source this data in a OLEDB Source component followed by Lookup and Conditional Split operator.

Making use of a Lookup and Conditional Split operator within SSIS will flag the records for INSERT's, UPDATE's and even DELETE's for you.

Thanks

Amol Naik