sql server 2005 integration services n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
SQL Server 2005 Integration Services PowerPoint Presentation
Download Presentation
SQL Server 2005 Integration Services

Loading in 2 Seconds...

play fullscreen
1 / 32

SQL Server 2005 Integration Services - PowerPoint PPT Presentation


  • 137 Views
  • Uploaded on

SQL Server 2005 Integration Services. Dave Glover Microsoft Australia http://blogs.msdn.com/dglover. Agenda. Cleanse Data Input Split an Output Channel Script to Branch Control Lookup Fuzzy Values Loop through Folder Configure and Deploy. Overview.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'SQL Server 2005 Integration Services' - barbra


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
sql server 2005 integration services

SQL Server 2005Integration Services

Dave Glover

Microsoft Australia

http://blogs.msdn.com/dglover

agenda
Agenda
  • Cleanse Data Input
  • Split an Output Channel
  • Script to Branch Control
  • Lookup Fuzzy Values
  • Loop through Folder
  • Configure and Deploy
overview
Overview
  • Part of SQL 2005 Std and Ent Editions:
      • Integration Services with Basic Transforms. Provides graphical extract, transform, and load (ETL) capabilities
      • Ent only: Integration Services Advanced Transforms Includes data mining, text mining, and data cleansing
  • Collaborative Development
  • Separate Management Tools
  • Data Quality and profiling
  • Scalable
slide4

FILE

HTTP

WMI

WMI Event Task

XML Task

Web Services Task

Variables

Log Providers

Task Host

Custom Task

Containers

Event Handlers

Send Mail Task

Variables

Log Providers

FILE …

Data Flow Task

ODBC …

FTP Task

FTP

SSIS Runtime

Sources

Dests

OLEDBConnection FTPHTTPFILE ODBCManagers CUSTOM FLATFILE

OLEDB Connection FTPHTTPFILE ODBCManagers CUSTOM FLATFILE

SQL

Server

DB

WMI

XML

XML

Flat File

Flat File

WEB

FTP

File

FTP

Custom

Custom

cleansing data input
Cleansing Data Input
  • Connection-Related Objects
  • Connection Manager
  • Flow Types
  • Data Adapter
  • Metadata Validation
  • Data Flow Components
flow types
Flow Types
  • Two different types of flow
    • Control Flow = Runtime = Tasks
    • Data Flow = Pipeline = Transforms
  • Managed in Designer
    • Used to be single view in SQL 2000
    • Separate views in SQL 2005
  • Control Flow handles tasks and precedence
  • Data Flow handles transformations - zoomed contents of Data Flow task
data flow components

Source

Transform

Destination

Data Flow Components
  • Components
  • Paths
    • Data route between one component and the next
    • Includes metadata about columns moving around
    • Lineage Identifier tracks item transformations
  • Pipeline
    • Components connected by a path
splitting an output channel
Splitting an Output Channel
  • Distributors and Collectors
  • Precedence Constraints
distributors and collectors

Tfm

Tfm

Src

Dst

D

C

Tfm

Distributors and Collectors
  • Distributor transformations
    • Multicast
    • Conditional Split
  • Collector transformations
    • Union All
    • Merge
    • Merge Join
precedence constraints
Precedence Constraints
  • Connect one task to another
  • Give sequential relationship to tasks
    • Success/Failure/Completion workflow
    • Establish concurrency
  • Connected sequence of tasks is a task list
    • Independent Task Lists execute concurrently
    • Tasks within a list execute sequentially
slide11

Demo

Cleansing Data &

Splitting an Output Channel

script and branch control
Script and Branch Control
  • Package Variables
  • Script Task
  • Complex Precedence
  • Containers
package variables
Package Variables
  • Scope
    • Each container can have variables
    • Define namespace for user variables
    • Containers can access variables from higher levels
  • Accessible from
    • Expressions (such as loops and constraints) – Use @
    • Parameters in Execute SQL task
    • Parent Package (as part of configuration)
    • Script
script task
Script Task
  • Currently VB .Net
  • Can read or modify properties throughout the package
  • Can’t access inner workings of tasks or transforms
  • Can’t modify pipeline metadata (e.g., number of columns piped)
containers
Containers
  • Container provides
    • Grouping of task lists (list of one is allowed)
    • Transaction scope
    • Variable scope
  • A package is a container
    • Add your own tasks
    • Insert your own containers
    • Loops are containers, too
fuzzy lookup transformation
Fuzzy Lookup Transformation
  • Proximity algorithm to find matches
  • Builds index – Index can persist
  • Creates metrics
    • Similarity
    • Confidence
  • Uses a separate connection for reference table
loops
FOR LOOP

Loops while expression is TRUE

Manually add loop counter

Init: @N = 1

Eval: @N <= 25

Increment: @N =@N + 1

Execute tasks in container on each iteration

More control—and more complex than For Each

FOR EACH LOOP

Loops over set of objects

Files

XML nodes

Database objects

Set variable (e.g. file name) for each iteration

Execute tasks in container on each iteration

Loops
slide18

Demo

Script and Branch Control

configure and deploy
Configure and Deploy
  • Configurations
  • XML Customizability
  • Deployment
  • Execution
package lifecycle support
Package Lifecycle Support
  • Data Visualizers
  • Debugging
    • Stop execution during package
      • Stop control flow before and after
      • Stop transformation during (visualizer)
  • Logging
    • Event Handlers
    • Log Providers
configurations
Configurations
  • Take something from the system
    • Environment variables, registry, XML option file
    • Apply it to some part of your package
    • Run the package with the new setting
  • Useful for multiple “similar” jobs
    • Similar to Dynamic Properties from SQL 2000
    • Facilitate reusability, different environments
  • Configurable at runtime or during execution
deployment
Deployment
  • Same server deployment
    • Deploy from BI Development Studio
  • Multiple server deployment
    • Deployment Utility
    • Package configuration files with package
    • Deploy to SQL Server (msdb) or file (dtsx)
execution
Execution
  • Command-line execution
    • DTEXEC
  • User Interface execution
    • DTEXECUI
    • Can generate command line for DTEXEC
  • Scheduling
    • SQL Server Agent
slide24

© 2004 Microsoft Corporation. All rights reserved.

This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

report builder architecture
Report Builder Architecture

Report Builder

Client

Model

Designer

Report

Manager

SQL Mgmt Studio

Report

Designer

Web Service / URL interface

Report Server

Drill through report generation

Query generation

Model Security

Data Sources

(SQL Server,

Analysis Services)

Report Processing

Query

Security

Rendering

Delivery

SQL Server Catalog

why report builder

Report Consumers

Business Users

Power UsersDevelopers

Why Report Builder?

Report Viewer

Report Builder

Report Designer

what is report builder
What is Report Builder?
  • A new ad-hoc report design tool for SQL Server Reporting Services
  • Targeted at business users who want to find and share answers to interesting questions
  • Driven from a business model of the data so users do not need to understand the underlying data structures
  • Not a full analytical client or replacement for Pivot Tables
  • Fully integrated with Reporting Services and delivered in SQL Server 2005
wrap up
Wrap-up
  • Report Builder is…
    • a new ad hoc report design tool for SQL Server Reporting Services
    • targeted at business users who want to find and share answers to interesting questions
    • driven from a business model of the data so users do not need to understand the underlying data structures
    • is fully integrated with SQL Server Reporting Services and delivered in SQL Server 2005
slide32

© 2003-2004 Microsoft Corporation. All rights reserved.

This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.