SQL Server 2014SSIS and SSRS

Sql Server Integration Services• The Big Picture • Control Flow • Data Flow Sources and Destinations • Data Flow Transformations • Making packages dynamic • Containers and Advanced Control Flow Tasks • Package Reliability • Advanced Scripting • Deploying to the SSIS Catalog

Sql Server Reporting Services• Introduction• SSRS Overview and Tools • Working with Solutions and Projects • Creating Basic Reports • Formatting Reports • Custom Expressions • Summarizing and Sorting • Add Flexibility with Parameters • Enhanced Report Items • Using Report Manager • Using SQL Server 2014 Report Builder

Module 1:The Big Picture

– Microsoft Business Intelligence Suite– The SSIS Architecture– Import/Export Wizard– Introducing SSDT-BI

Microsoft Business Intelligence Suite

SSI Architecture

Import Export Wizard

Introducing SSDT-BI

Lab Activities :Exploring a Data Warehousing Solution

• Exercise 1: Exploring Data Sources• Exercise 2: Exploring an ETL Proces

s• Exercise 3: Exploring a Data Wareho

use

Module 2 : Control Flow

– Introduction to the Control Flow– Understanding Precedent Constraints– Grouping and Annotating Tasks– Working with Properties– Favorite Tasks– Common Tasks

Introduction To Control Flow

Understanding Precedent Constraints

Grouping and Annotating Tasks

Favorite Tasks,common tasks• SSIS Toolbox, control flow and data

flow components are organized into categories

• Favorites and Common categories appear in the toolbox when you select the Control Flow, Data Flow, and Event Handlers tabs.

• Other Tasks category appears in the toolbox when you select the Control Flow tab or the Event Handlers tab.

Module 3: Designing and Implementing a Data Warehouse

Designing and Implementing a Data Warehouse

Dimensional Modeling

Documenting Dimensional Models

Documenting Dimensional Models

Considerations for Dimension Keys

Dimension Attributes and Hierarchies

Unknown and None

Designing Slowly Changing Dimensions

Time Dimension Tables

Time Dimension Tables

• employee dimension might consist of manager

Module 4 : Data Flow Sources and Destinations

– Working with Data Sources– Working with Data Destinations

Working with Data Sources• Demonstration : Working with Data

Sources

Working with Data Destinations

• Demonstration : Working with Data Destinations

Lab Activities :Creating an ETL Solution with SSIS

Exercise 1: Exploring Source DataExercise 2: Transferring Data by Using a Data Flow TaskExercise 3: Using Transformations in a Data Flow

Module 4 : Data Flow Transformations

– Basic Transformations– Blocking Transformations– Advanced Transformations– Managing Incremental Updates

Basic Transformations

Blocking Transformations

•  three types of transformations– non-blocking, semi-blocking or fully-blocking

• Synchronous : same buffer as the input– All destination adapters

• Asynchronous : uses a new buffer– All source adapters

Advanced Transformations

Managing Incremental Updates

Module 5 : Making packages dynamic

– Using Variables– Understanding Package Parameters– Understanding Project Parameters– Working with Expressions

Using Variables

• Variables can be user defined,system defined

• Demonstration : using Variables

Using package parameters,project parameters

• Demonstration : using Variables,working with expressions

Lab Activities : Implementing Control Flow in an SSIS Package

• Lab A: Implementing Control Flow in an SSIS Package

Module 6 : Advanced Control Flow Tasks

– Working with Containers– Adding Custom Control Flow Tasks– The "Other Tasks"– Working with Change Data Capture (CDC)

Working with Containers

o Containers are objects in SQL Server Integration Services that provide structure to packages and services to tasks

o Foreach Loop Containero Runs a control flow repeatedly by using an

enumerator

o For Loop Containero Runs a control flow repeatedly by testing a

condition.

o Sequence Containero Groups tasks and containers into control flows that

are subsets of the package

o Task Host Containero Provides services to a single task.

Custom Control Flow Tasks

o Microsoft SQL Server Integration Services  (SSIS) provides powerful Extract-Transform-Load (ETL) and in 99% of the time sufficient for most companies.

o There are however a few situations where out-of-the-box tasks do not provide the functionality needed

o visit you favorite search engine and look for a vendor that has already made it

o Or  write one yourself

Working with Change Data Capture

o CDC records insert, update, and delete activity that is applied to a SQL Server table.

o  source of change data for CDC is Transaction Logo As inserts, updates, and delete done, entries are

added to logo  log serves as input to the capture process

Lab Activity : Implementing a Data Extraction Solution

• Exercise 1: Using a Datetime Column to Incrementally Extract Data

• Exercise 2: Using Change Data Capture• Exercise 3: Using the CDC Control Task• Exercise 4: Using Change Tracking

Module 7 : Package Reliability

– Configuring Logging– Creating Event Handlers– Redirecting Rows– Using Checkpoints– Configuring Transactions

Configuring Logging• Demonstration : Configuring Logging

Event Handlers

• Demonstration : – Creating Event Handlers

Redirecting Rows• configure the component to redirect the

failed row to error output.• Demonstration : Redirecting Failed Rows

Lab B: Using Transactions and Checkpoints

• Exercise 1: Using Transactions• Exercise 2: Using Checkpoints

Deploying to the SSIS Catalog

– Introduction to the SSIS Catalog– Creating Environments– Deploying Projects– Backwards Compatibility– Executing Packages in SQL Server

Management Studio (SSMS)– Executing Packages from the Command Line

SSIS Catalog,Environments• SSISDB catalog is the central point for

working with Integration Services • objects that are stored in

the SSISDB catalog include projects, packages, parameters, environments.

• Packages can be deployed across multiple environment(test,production)

Deploying Projects

Executing Packages in SQL Server Management Studio

Executing Packages from the Command Line