SSIS 2K5 - Merging Data Between Databases
-
I have two databases on different servers namely MAINDB and SUBDB.
MAINDB is a databases that hold all data, where as SUBDB only contains active data for a certain period.
Every morning Semi-Static Data (Products, Customers etc) from MAINDB needs to be synced with SUBDB. This is a case of new data must be inserted, or existing data updated.
At the end of day, all transaction performed on the SUBDB needs to be synced back to the MAINDB. Here the process is easy as data is selected from SUBDB by ItemDate and then inserted ion MAINDB in a bulk manner.
My Problem
SSIS doesn't have a specific built in process that allows me to merge/sync two tables (Products/Customers etc.), but apparently there are several processes one can combine to achieve this.
So I need help on what steps/processes i need to do to achieve this.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font] -
One would think with all the intelligence at Microsoft they would be able to provide us with a very simple item that enables the data two tables to be merged with ease ... but no ... instead they created things like VISTA and ruined SKYPE.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font] -
Not too difficult to do in SSIS, though I don't see why you would - it's easier to write a SQL stored proc to do it.
Using SSIS, first put an Execute SQL Task, and do a query similar to "UPDATE sSET m.Fields = s.Fields FROM SubDB m JOIN MainDB s ON m.ID = s.ID". For the insertion of new records, put a Data Flow task with an OLE DB source selecting the records from MainDB for the day. Then put a lookup transform, and lookup records with a query similar to "SELECT ID FROM SubDB". Join the records to MainDB on ID. Then connect the error output to your OLE DB Destination, making sure to choose redirect rows on error for the Lookup. This will write new records to SubDB.
-
Sounds like a plan.
But please note that the databases are on different servers.
This is why I think SSIS is needed, instead just stored procedures.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font] -
If they are different servers it gets a bit tricker yeah. Then SSIS might be the better option - should be able to work with what I've given you though.
-
kramaswamy (1/9/2012)
Using SSIS, first put an Execute SQL Task, and do a query similar to "UPDATE sSET m.Fields = s.Fields FROM SubDB m JOIN MainDB s ON m.ID = s.ID".Will this work for different servers?
Have to admit I am kinda new to SSIS, and will be doing a course in February. But my company needs answers now.
Still it baffles me how there could not be a simple solution Drag and Drop Item (like Lookup) to be able to do this.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font] -
It won't. Unless you have a linked server that is.
SSIS is not very good at doing updates. You could do it using a SQL Command, but that would involve updates for each row.
What you would be better off doing is transferring all the records from MainDB to SubDB into a staging table, then doing the updates and inserts all locally.
-
I was actually thinking the same thing. Where data is transferred (bulk insert) to an empty temp table. And then using Stored Procedures one could perform complex T-SQL update/insert processes.
Thnx
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font] -
If you can use third-party solutions, check the commercial CozyRoc Table Difference component. It solves the exact requirement you have.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
I don't think my company will pay that amount of many for a additional tool. 🙁
Thanks for the suggestion though.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply