January 3, 2013 at 1:57 am
We have 2 databases Source Database and Destination Database, We want to transfer data from source table records to destination tables immediately .
Can anyone provide tips to implement this by using Integration services ?
Regards,
Ram
January 3, 2013 at 2:04 am
January 3, 2013 at 2:07 am
Sorry - ignore my post I misread what you had written
January 3, 2013 at 2:10 am
sram24_mca (1/3/2013)
We have 2 databases Source Database and Destination Database, We want to transfer data from source table records to destination tables immediately .Can anyone provide tips to implement this by using Integration services ?
Regards,
Ram
Integration Services does not sound like the right tool for the job - it's not an always-on, data-drip-feed type solution. Have you considered CDC or replication instead?
Rather than 'immediately', can you specify the maximum acceptable time between the data appearing in source and being copied to destination?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 3, 2013 at 2:11 am
Ram
Use the Import/Export wizard. You have the option to run it immediately or save it as an SSIS package.
John
January 3, 2013 at 2:14 am
Phil Parkin (1/3/2013)
Integration Services does not sound like the right tool for the job - it's not an always-on, data-drip-feed type solution. Have you considered CDC or replication instead?Rather than 'immediately', can you specify the maximum acceptable time between the data appearing in source and being copied to destination?
Phil, I took "immediately" as meaning he wants to do it now rather than that he needs the source and destination to be always synchronised. I think we're going to need clarification.
John
January 3, 2013 at 2:15 am
John - I think he wants the package to run each time data is added/updated to a table
I think, as Phil has suggested, that replication might be his best option
January 3, 2013 at 2:17 am
Hi Parkin,
Consider the source data has to copied to destination with in 3 Minutes.
Regards,
Ram
January 3, 2013 at 3:05 am
sram24_mca (1/3/2013)
Hi Parkin,Consider the source data has to copied to destination with in 3 Minutes.
Regards,
Ram
OK - and what about replication or CDC?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 4, 2013 at 5:55 am
Hi Parkin,
Thanks, Can you please explain the CDC Concepts? I am new to this CDC Concept. Thanks in advance
Regards,
Ram
January 4, 2013 at 6:00 am
What edition of SQL server are you running?
2005/2008 Standard / Enterprise
CDC is a 2008 onwards and Enterpise only feature
http://msdn.microsoft.com/en-us/library/bb522489%28v=sql.100%29.aspx
If using standard, you would need to use replication.
January 7, 2013 at 2:01 am
The other option to consider is Triggers on the source table that fire a replication script. How is the source table updated (e.g. Bulk load or data import from SSIS using some sort of Transform, or ad-hoc from an application/scheduled web-scrape)
Another alternative would be to just have an SQL server Agent job that runs every 3 minutes and this will fire every time, whehter there is data to transfer or not.
Does the data need to go through any transformation. If not, could you just make the destination server a linked server and read the data from the source table on demand.
Each option has its own performance and maintenance issues.
January 7, 2013 at 10:13 pm
aaron.reese (1/7/2013)
The other option to consider is Triggers on the source table that fire a replication script.
Probably best to stick to standard replication; this has better performance overall than using triggers. Triggers can also introduce some rather interesting logic errors if you're not careful.
aaron.reese (1/7/2013)
Another alternative would be to just have an SQL server Agent job that runs every 3 minutes and this will fire every time, whehter there is data to transfer or not.
I've done this myself, but I would treat it as the last resort option, if you can't use replication.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply