0

I have to replicate my source DB in MS SQL SERVER to target SQL SERVER. After an initial Transfer database task, I'm incrementally loading new or updated rows using lookup. The problem is I have to do this for each & every table in the database & my database has around 70-80 tables. Is there a way I could scroll through all the tables in the database & incrementally load each one of them or even better I could incrementally load on database level ?

Manish Kumar Thakur
  • 103
  • 2
  • 10
  • Are you really just replicating the database? To the degree that actual [replication](https://msdn.microsoft.com/en-gb/library/ms151198.aspx) might be more appropriate than SSIS? – Matt Gibson Apr 04 '16 at 09:58
  • We have no control over the source database. We can just execute select queries as only read permission is available to us. So using replication may not be an option. – Manish Kumar Thakur Apr 04 '16 at 11:46
  • You can also try SQL Server Import and Export Wizard: http://stackoverflow.com/questions/27343061/copying-multiple-tables-using-ssis-package/27344589#27344589 And here is some great biml example by billinkc: http://stackoverflow.com/questions/20222388/ssis-best-practice-to-load-n-tables-from-source-to-target-server – helix Apr 06 '16 at 13:06

1 Answers1

1

This sounds like a good candidate for BiML, which lets you dynamically create SSIS packages based on meta data.

There is currently NO built-in way in SSIS to loop through tables that have different column structures and incrementally load them with the same set of SSIS objects. You would have to build SSIS objects for every table.

Another thing you might look into is Replication, if you are using SQL Server on both sides of the ETL. That would be the closest thing to "incrementally loading on the database level".

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52