Solved

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

Posted on 2014-03-05
3
389 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
I have a large data set and a SSIS package. How can I load this file in multi threading?
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

756 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