CODEWITHSUNDEEP
databasessissql-server-2012integrationdata-warehouse
RMD
RMD

Reputation: 13

Using SSIS to integrate data between two databases

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?

Upvotes: 1

Views: 564

Answers (1)

Mario Tacke
Mario Tacke

Reputation: 5488

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.

Upvotes: 1

Related Questions