Search code examples
sql-serverssisetlssis-2012

SSIS ETL - Is it a good practice to have the destination DB pull data from sources directly

I have an ETL package that moves data from a number of source SQL Server DBs to a single destination SQL Server DB. All these DBs are on the same server. The destination DB contains a large number of views that reference the source DBs. E.g. SELECT * FROM SourceDB1.dbo.Transactions.

So the majority of the data goes directly source DB => destination DB, without passing through the SSIS server. I'm new to SSIS and wondering if this is a good thing to do, or should I look into changing the process.

Solution

  • When I started learning about ETL and data migration using SSIS I was always told that it is best practice to first move the data into a staging database where you can validate the data, deduplicate, clean etc in there then move it to the destination DB