Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Shirley
Starting Member
3 Posts |
 Posted - 2010-01-26 : 10:25:45
|
| Hello,I'm new to SSIS. I need help to direct me where to start.There are 2 SQL servers: A and B. SQL server A is 2005 while SQL server B is 2000.SQL Server A has database: shoppingcart A.SQL Server B has database: shoppingcart B.shoppingcart A has 3 tables:RegisteredUsersA, OrdersA, OrderItemsAshoppingcart B has 3 tables:RegisteredUsersB, OrdersB, OrderItemsB, RegisteredUsersA and RegisteredUsersB are NOT identical tables.OrdersA and OrdersB are NOT identical tables.OrderItemsA and OrderItemsB are NOT identical tables.I need to transfer data from shoppingcart A tables to shoppingcart B tables when there is an order saved into shoppingcart A database. Calcautation and query are needed in order to transfer correct data. The average transaction is 10 per day. Questions:1. Should I setup a trigger on OrderA table, whenerever this table is updated?2. Should I use SSIS to transfer data from shoppingcart A to shoppingcart B? If yes, how can I start in SSIS, control flow, data flow?3. Is there any other way to achieve this?Your help is appreciated.Thanks,Shirley |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-26 : 10:45:51
|
| if you dont want this to happen at real time, you can schedule a nightly job which executes ssis package which transfers only changed (new/modified) data from tables to corresponding ones in shoppingcartB . You may require audit columns on your tables to identify changed data (delta change) each day. |
 |
|
|
Shirley
Starting Member
3 Posts |
Posted - 2010-01-26 : 13:13:26
|
| Hi,Thanks for your reply.The transaction needs to be run at real time. So should I use trigger to start the transaction? And can trigger trigger a stored procedure? If yes, then i don't need SSIS. Your help is appreciated.Thanks,Shirley |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-26 : 13:23:28
|
quote:
Originally posted by Shirley
Hi,Thanks for your reply.The transaction needs to be run at real time. So should I use trigger to start the transaction? And can trigger trigger a stored procedure? If yes, then i don't need SSIS. Your help is appreciated.Thanks,Shirley
if real time, i think you need trigger.whats the need of ssis here? isnt it just a matter of cross db insert? |
|
|
|
Shirley
Starting Member
3 Posts |
Posted - 2010-01-26 : 14:09:01
|
| Hi,That's why I'm not sure whether I need SSIS or not.Is it possible to setup a trigger and then trigger a store procedure?Your help is appreciated.Thanks,Shirley |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-27 : 11:19:28
|
quote:
Originally posted by Shirley
Hi,That's why I'm not sure whether I need SSIS or not.Is it possible to setup a trigger and then trigger a store procedure?Your help is appreciated.Thanks,Shirley
why you need separate sp? isnt it enough to write logic in trigger itself? |
|
|
|
|
|
|
|
|
should I use SSIS?