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 |
|
sqlpal2007
Posting Yak Master
200 Posts |
 Posted - 2011-10-12 : 13:56:19
|
| Hello All,I am working on a SSIS package which extracts the data from production SQL 2008 database to DW 2008 database for 40+ tables.I could successfully add new rows and update existing rows to destination (DW) database. But I don't knwo how to work on deletes of source. If any records deleted at source how to refresh them at destination.Can anyone help me on this?Thanks,-S |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-12 : 14:06:23
|
| just add a lookup transformation from destination to source. any rows coming through no match output will be ones that have been deleted fro source. Add a OLEDB command to delete those rows from destination based on primary key combination------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
sqlpal2007
Posting Yak Master
200 Posts |
Posted - 2011-10-12 : 14:13:43
|
| I already have a OLEDB source connected to Lookup and the lookup inserts the new rows in the destination. Where should I add the another lookup now? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-12 : 14:25:24
|
| you should be adding a new data flow for that. use oledb source to connect to your destination and add a lookup to source (just reverse of what you did in current data flow). lookup should be again based on pk columns------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
sqlpal2007
Posting Yak Master
200 Posts |
Posted - 2011-10-12 : 15:10:22
|
| Thanks visakh16I created a new data flow task and in that a OLE DB source which connects to lookup and then OLE DB destination which dumps data to the staging table. And then Execute SQL task (SQL statement to delete the non existing rows - delete s from source s inner join stagedeletes d on s.id = d.id) which has connection from Data flow task. Thsi works but I am not sure if this is the correct approach though?Also, how do I achieve this insert new / updates / deletes for 40 some tables? I have to refresh the destination database every 24 hours. Is it better to refresh the delta or empty the detination records and reload the whole data.Please advise. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-13 : 01:47:10
|
| the approach sounds fineFOr 40 + tables you need 40 data flows to do it. if your attempt is to refresh full db as it as why not take a latest backup and restore it each day after deleting existing db?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Remove rows from the destination