etl in data warehouse as a service enviornment

ETL in Data Warehouse As a ServiceSANIL MHATRE | DATABASE ENGINEERING | LUMERIS

@SQLSUPERGURU | HTTP://SQLWITHSANIL.COM/

Data Warehouse As a Service

Client specific

Bulk Data

Client specific Real time data

Common Data Sources

Client Specific Data Marts

USER INTERFACE

ANALYTICS

Current Technology Stack

• SQL Server Database Engine

• SQL Server Integration Services

• SQL Server Analysis Services

• SQL, MDX, C# , ASP.NET

• Team Foundation Server, Visual Studio

• DACPAC & ISPAC Deployments

Future • Hadoop

• Hbase

• MongoDB

• Java

• HiveQL, Pig Latin, Spark

• Team Foundation Server, GIT

• Jenkins, ANT

SSIS Catalog & Project Deployment Model

◦ SSISDB - SQL Server Database , a new repository model for Integration Services (SQL Server 2012 onwards)

◦ Folder Hierarchy for Projects and Environments

◦ Project Based Deployment

◦ Versioning

◦ Validation

◦ Execution & Logging

◦ Environments

◦ Reports

◦ Permissions

SSIS Catalog Creation

Project Deployment ModelIntegration Services Project Conversion Wizard

Deploying to the CatalogIntegration Services Deployment Wizard

ConfigurationNo Wizard ! But pretty intuitive

Executing Package via SQL Agent Job

ResourcesSSIS catalog - https://msdn.microsoft.com/en-us/library/hh479588.aspx

Creating the SSIS Catalog - https://msdn.microsoft.com/en-us/library/gg471509.aspx

Backup, Restore & Moving the SSIS catalog - https://msdn.microsoft.com/en-us/library/hh213291.aspx

SSIS Project Deployment Model - https://msdn.microsoft.com/en-us/library/hh213290.aspx

Converting SSIS Packages to Project Deployment Model - https://msdn.microsoft.com/en-us/library/ff929158.aspx

Thank youQuestions ?

Comments ?

Feedback ?

@SQLSUPERGURU | http://sqlwithsanil.com/