Solved

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

Posted on 2014-03-05
3
386 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

895 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

17 Experts available now in Live!

Get 1:1 Help Now