Transcript Introduction

Integration Services in SQL Server
2008
Allan Mitchell – SQLBits – Oct 2007
1
Introduction
•
•
•
•
•
•
New threading architecture
Pipeline limiter (not really new!)
New Tasks/Components/Enumerators
C#
Change Control routines for data extraction
Q and A
2
Who am I?
•
•
•
•
SQL Server MVP
Co author on Wrox book on SSIS
www.SQLDTS.com, www.SQLIS.com
Consultancy company – Konesans
(www.konesans.com)
3
What I will not cover
• Finding SSIS
• Creating packages
• General package design
4
Threading
• How is it now?
– Some processes use single threads
• Why is this not brilliant?
– Sync Outputs with a blocking transform on one of the
outputs kills performance
• What is coming?
– Threading model changed to allow components to go out
and try to get their own thread
• Why will this help?
– Takes advantage of today’s bigger, faster boxes
5
DEMO
6
Pipeline Limiter
• Why would they want to slow me down?
– Fill buffers and run out of memory
• I see it now in SQL Server 2005? Really?
– You see it but SSIS keeps quiet
7
DEMO
8
New Tasks and Components
(Data Collector Specific)
• Tasks
– Update Row Counts
– Reset Row Counts
– Trace Start Script
– Cache Window Maintenance
• Components
– TxPerfCounters
9
New Enumerator (s)
• ForEach Database Enumerator
– Uses OLEDB for connection
– Configure what DBs you enumerate (see next
slide)
– Rubbish UI experience
10
DB Enumeration Types
DBENUM_CUSTOM
=0
DBENUM_SYSTEMONLY = 1
DBENUM_USERONLY = 2
DBENUM_ALL
=3
11
DEMO
12
C# !
Script Component
• Script Task
• Custom component development has
always been available in C#
•
13
DEMO (Quick Look)
14
Using New DB Change Control
•Billed as an ETL feature
•We’ll use it if it’s there
•What is it?
•How do I use it then?
15
What and Why
•
•
•
•
Increased efficiency of ETL Process
Incremental Extractions
Identify changed rows and columns
Identify operation on data, all changes or net
changes
• Log Based (Transactional Replication LR)
• Lightweight
16
Terminology
• Capture Instance – Base Object (Max 2
per object)
• Capture Process – Reading of the log and
placing the rows into the change tables
17
DEMO
18
Questions?
Email: [email protected]
19