Slide 7
Slide 7 text
SOME TYPICAL ETL APPROACHES
Entirely contained in SQL Server DB
Source is SQL Server only and cross DB queries are allowed
Stored procedures are used
Pros: easy to code, modularize and maintain; CI/CD is easier and potentially easy to migrate to cloud
Cons: might require synonyms, cross-db transactions, excessive locking, difficult debugging; some use linked servers as well
Entirely contained in SSIS
Multiple types of sources
Flow and logic is controlled via packages
Pros: modules and ability to combine different sources, checkpoints, transaction management outside T-SQL, logging, debugging, scripting in C#, can
be moved to a different server, scaleout, can use external processes (cmd, ftp, etc.)
Cons: additional knowledge, customizations are difficult, CI/CD is not straight forward
Two strategies
Master-child package (DEMO!)
Individual packages
Package vs Project deployment of SSIS