Using SSIS to integrate data between two databases

Question

We have two SQL Server application databases which currently share data via a point to point SQL Query (a pull from A to B). This SQL, running in database B, is used to periodically extract, transform and load data from a number of tables in database A into tables in B; effectively implementing a number of business rules in the process. Unfortunately, this process is very fragile with loads frequently failing, very little auditing etc resulting in endless tweaking of the SQL Script. Long story short we are looking for other ways to implement the data integration between these two databases.

One of the things we are looking at is to use SSIS to periodically load data from Database A into Database B. We already use SSIS to extract data from Database A to a Data Warehouse where it is used for querying, analytics etc. The data here is almost identical to the ETL we would do for the integration between A and B.

My question is, what is good practice in this situation? Could we re-use the Data Warehouse as a point of integration for B (i.e. pull data from the Data Warehouse to B) or is this bad practice making the Data Warehouse part of the integration architecture of the company?

if replication is an option you can use,then i recommend using it ,Further please post more info,here i dont understand whether you want only data after applying business rules or same data from A to B — TheGameiswar
– TheGameiswar, Commented May 6, 2016 at 14:52
You could use staging tables in your ETL, which hold common data relevant to both the Data Warehouse and Database B. Then load both from the staging tables. — RichardCL
– RichardCL, Commented May 6, 2016 at 16:14
You should consider recording a history of the batches that you are processing, plus lineage on the records. — RichardCL
– RichardCL, Commented May 6, 2016 at 16:15

Mario Tacke · Accepted Answer · 2016-05-06 14:50:51Z

1

As with many things: it all depends. Using a data warehouse as integration source is just as valid as using a non-warehouse database. What it comes down to is accuracy and refresh intervals. Some questions you should ask yourself before using one data source over another:

Do I need accurate data?
Is my data source updated frequently enough?
Which data source is the source of truth?
Do I need access to very old historical data?
...

Using a data warehouse is not bad practice.

answered May 6, 2016 at 14:50

Mario Tacke

5,5073 gold badges34 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using SSIS to integrate data between two databases

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related