Transcript Module 2: Authoring Basic Reports

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
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
SSIS Runtime
Sources
Dests
Custom Task
Containers
WMI
WMI Event Task
Event Handlers
Send Mail Task
Log Providers
Variables
ODBC …
FILE …
Data Flow Task
OLEDB
XML Task
SQL
Connection FTP HTTP
Managers CUSTOM FLATFILE
Log Providers
Task Host
FILE ODBC
FTP
FTP Task
Variables
FILE
Custom
HTTP
Flat
File
WEB
FTP
Connection FTP HTTP
Managers CUSTOM FLATFILE
XML
OLEDB
WMI
FILE ODBC
DB
Web Services Task
Server
XML
Flat File
File
FTP
Custom
Cleansing Data Input
Connection-Related Objects
Connection Manager
Flow Types
Data Adapter
Metadata Validation
Data Flow Components
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
Components
Source
Transform
Destination
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
Distributors and Collectors
Precedence Constraints
Distributors and Collectors
Distributor transformations
Multicast
Conditional Split
Collector transformations
Union All
Merge
Merge Join
Tfm
Src
D
Tfm
Tfm
C
Dst
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
Demo
Cleansing Data &
Splitting an Output Channel
•Script and Branch Control
Package Variables
Script Task
Complex Precedence
Containers
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
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
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
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
Demo
Script and Branch Control
Configure and Deploy
Configurations
XML Customizability
Deployment
Execution
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
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
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
Command-line execution
DTEXEC
User Interface execution
DTEXECUI
Can generate command line for DTEXEC
Scheduling
SQL Server Agent
© 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 Overview
Report Builder Architecture
Report
Manager
SQL Mgmt
Studio
Report
Designer
Report Builder
Client
Web Service / URL interface
Report Server
Drill through report generation
Data Sources
(SQL Server,
Analysis Services)
Query generation
Model Security
Report Processing
Query
Security
Rendering
Delivery
SQL Server Catalog
Model
Designer
Why Report Builder?
Report
Consumers
Business
Users
Power Users
Developers
Report Viewer
Report Builder
Report Designer
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
Report Builder
Report Builder vs. Report
Designer
Report Builder
Report Designer
Targeted at business users
Targeted at IT pros and developers
Ad hoc reports
Managed reports
Auto-generates queries using
semantic layer on top of the source
Native queries (SQL, OLE DB,
XML/A, ODBC, Oracle)
Reports built on templates
Free-form (nested, banded) reports
Click-once application, easy to
deploy and manage
Integrated into Visual Studio
Cannot import Report Designer
reports
Can work with reports built in
Report Builder
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
© 2003-2004 Microsoft Corporation. All rights reserved.
This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.