Solved

(ssis) to move certain tables with common where conditions to another DB

Posted on 2014-03-05
3
385 Views
Last Modified: 2016-02-10
is SSIS a good/right way do this scenario:

server A-> DB1 has 260 tables.
server B -> DB1 has 80 tables. These tables schema are from server A-> DB1.

we need to refresh data from
server A-> DB1 to
server B-> DB1 for 80 tables often. but only for a subset of data.

during a refresh, we need to truncate/delete the 80 tables in server B -> DB1.. then, load only those 80 tables fresh, but only selective records as below example:

example:
table1 - select * from table1 where key1 in (5 values...)
table2 - select * from table2 where key1 in (5 values...)
table3 - select * from table3 where key1 in (5 values...)
table4 - select * from table4 where key1 in (5 values...)
............
table79 - select * from table79 where key1 in (5 values...)
table80 - select * from table80 where key1 in (5 values...)
0
Comment
Question by:25112
  • 2
3 Comments
 
LVL 5

Author Comment

by:25112
ID: 39908364
if ssis, then should I hard code these 80 statements?

is the performance dependent on the # of records in each of these tables? some of these tables have only 100s of records, others have millions of records in server A-> DB1. But after the where condition, it can become very less, relatively speaking.
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39908375
>if ssis, then should I hard code these 80 statements?
Yes, regardless of the situation.  SSIS requires a 'contract' between itself and any data source, and cannot handle anything 'dynamic'.

>we need to refresh data from
> but only for a subset of data.
Define this.  If we're talking only the recent day's data + any changes, then a Change Data Capture source or Upsert would be more appropriate here.

>table1 - select * from table1 where key1 in (5 values...)
Any change these '5 values' can be in a table in the source, and then a view be created for each table that goes something like 'SELECT * FROM MyTable AWHERE key1 IN (SELECT key1 from the_list)?
0
 
LVL 5

Author Comment

by:25112
ID: 39910453
>>If we're talking only the recent day's data + any changes
No. everytime there is a refresh request, those 80 tables will be delted/truncated and only then SSIS run,

>>Any chance these '5 values' can be in a table in the source
Does this improve performance?
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now