Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2014-03-05
3
Medium Priority
?
401 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 66

Accepted Solution

by:
Jim Horn earned 2000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Loops Section Overview
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

578 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