SQL Server 2005
Integration Services
Franco Perduca
Factory Software
[email protected]
Introduzione
Cosa sono i SSIS?
Utilizzi
Architettura
Versioni
SSIS su piattaforme a 64bit
2
Cosa sono i SSIS?
Sono una piattaforma per costruire
soluzioni di integrazione dati.
Si tratta dunque di uno strumento ETL:
Extract
Transform
Load
Sono utilizzati in particolare nella
costruzione di datawarehouse, dove
spesso è necessario:
caricare ed integrare dati da fonti eterogenee
eseguire trasformazioni sui dati
3
Cosa sono i SSIS?
SSIS rimpiazza i DTS, presenti in SQL
Server a partire dalla versione 7.0
I SSIS includono:
Tool grafici per la creazione di package
Task per le funzionalità di workflow
Data source e data destination
Task per la trasformazione,
aggregazione, copia dei dati
4
Quando li utilizziamo?
Ecco alcuni scenari di utilizzo:
Integrazione di dati eterogenei
Popolamento di datawarehouse e datamart
Popolamento di fact table e dimensioni
Slowly changing dimension task
Pulizia e standardizzazione dei dati
Le funzionalità di trasformazione (anche via
script) sono strumenti potenti per questo
scopo
Automatizzazione di funzionalità
amministrative
Backup/restore, copia di oggetti di SQL Server,
process di cubi OLAP
5
Architettura di Integration Services
SSIS Service
Start e stop di package
Monitoraggio
Gestione package storage
SSIS runtime engine
Esecuzione package
Supporto per
Logging
Debug
Configurazione
SSIS data flow engine
Gestione buffer per il
movimento dati da source a
destination
Gestione trasformazioni
SSIS clients (object model)
Tool grafici e da command line
API
6
Tools
Sviluppo
Visual studio 2005
Business intelligence development studio
Amministazione/Esecuzione
SQL Server Management Studio
DTUtil
DTExec
7
SSIS e versioni SQL Server 2005
SQL Express
SSIS non è disponibile per questa versione
Workgroup
Solo import/export wizard
Standard
Mancano solo le funzionalità avanzate di Business intelligence
Enterprise/Developer
Versione completa di tutte le funzionalità
8
SSIS e piattaforme a 64-bit
Su server a 64bit le feature a 64bit sono installate nella cartella
Program Files, mentre le feature a 32 bit sono nella cartella
Program Files(x86)
Gli unici tool a 64bit sono dtexec.exe, dtutil.exe,
DTSWizard.exe
Alcuni driver non sono disponibili, ad es: Microsoft OLE DB
Provider for Jet
Problema con Export su Excel
Non è possibile disegnare package su Itanium
Gli script devono essere precompilati
Non è possibile utilizzare il task Execute DTS 2000 Package.
Run64bitRuntime = False in Project Properties se si vuol
eseguire il package con il runtime a 32bit
Il job di tipo SSIS Package Execution del SQL Server agent
utilizza sempre il runtime a 64bit.
Per schedulare un package a 32bit occorre creare un job di
tipo Operating System ed eseguire il package attraverso
dtexec.exe a 32bit
9
Oggetti e struttura di Integration
Services
Package
Control flow
Container
Task
Variable
10
Package
Il package è un oggetto contenente una
collection di oggetti interrelati.
Tasks
Connection
Managers
Precedence
Contraints
Log
Providers
Variables
Containers
For each
Enumerators
11
Tranform e
Adapters
Package
E’ il punto di partenza per lo sviluppo di
SSIS
Al package vuoto si aggiungono il control
flow ed i data flow.
E’ salvato in un file dtsx (xml) oppure
nell’msdb
12
Control Flow
Il package contiene un control flow che:
Definisce i precedence contraints di
Container
Task
13
Container
Organizzano il package in unità più piccole
Forniscono :
Transaction scope
Variable scope
Execution scope
Looping functions
Breakpoints
Error routing
Logging scope.
Il package stesso è un container.
14
Container
For Each loop
Esegue il workflow una volta per ciscun elemento di una
collection
For loop
Esegue il workflow finchè una condizione non diventi falsa.
Sequence container
Unità di organizzazione dei task
TaskHost
Contenier di ciascun task
Trasparente all’utente
15
Task
Eseguono il lavoro
Ogni task ha un compito specifico
DataFlow Task:
Sposta i dati
Trasforma di dati
Sono estensioni “pluggable”
Possono essere sviluppati da terze parti
16
Variables
Gli oggetti di Integration Services sono
isolati, non “vedono” gli altri oggetti
Le varibili sono utilizzate come
meccanismo di comunicazione tra gli
oggetti.
Possono contenere numerosi datatype:
Stringhe
Numeri
Date
Dataset
Oggetti
17
Variables
Possono avere scope diversi:
Package
Container
Task
Aiutano a rendere i package più
configurabili e flessibili.
Esistono numerose variabili di sistema
18
Connection Managers
Forniscono un link al mondo estero a SSIS
Alcuni Connection manager non
forniscono una vera e propria connessione
(ma solo un nome di un file)
Altri conneciton manager effrono
numerose informazioni oltre la
connessione.
Esistono numerosi connection manager:
OLEDB
File
SQL Server
Analysis Services
Oracle
XML
SAP
HTTP
19
Log provider
Ogni oggetto ha accesso all’infrastruttura
di logging.
I log provider sono componenti
“pluggable” che raccolgono le
informazioni di log dei componenti e le
scrivono in una determinata destinazione.
I componenti dei SSIS mandano i loro log
al runtime SSIS e non conoscono nulla dei
log provider.
Quindi i log provider rendono
indipendenti i componenti di SSIS dal log.
20
Event Handlers
I package ed i container (i loop e i task)
generano eventi che possono essere
intercettati attraverso gli EVENT
HANDLERS
Se un evento non è gestito dal proprio
container, esso è rilanciato nel container di
livello superiore nella gerarchia.
Un event handler è simile ad un package:
Ha task, data-flow e control flows
21
Eventi
Evento
Descrizione
OnError
This event is raised by an executable when an error occurs.
OnExecStatusChanged
OnPostExecute
This event is raised by an executable when its execution status changes.
This event is raised during the validation and execution of an executable to report
information. This event conveys information only, no errors or warnings.
This event is raised by an executable immediately after it has finished running.
OnPostValidate
This event is raised by an executable when its validation is finished.
OnPreExecute
This event is raised by an executable immediately before it runs.
OnPreValidate
This event is raised by an executable when its validation starts.
This event is raised by an executable when measurable progress is made by the
executable.
This event is raised by an executable to determine whether it should stop running.
OnInformation
OnProgress
OnQueryCancel
OnTaskFailed
OnWarning
This event is raised by a task when it fails.
This event is raised by an executable when the value of a variable changes. The event is
raised by the executable on which the variable is defined. This event is not raised if you
set the RaiseChangeEvent property for the variable to False. For more information, see
.
This event is raised by an executable when a warning occurs.
OnError
This event is raised by an executable when an error occurs.
OnExecStatusChanged
This event is raised by an executable when its execution status changes.
This event is raised during the validation and execution of an executable to report
information. This event conveys information only, no errors or warnings.
OnVariableValueChanged
OnInformation
22
Tipi di dati
SSIS ha tipi di dati proprietari. Quando i
dati sono caricati, essi sono convertiti nel
data type di SSIS.
Questo rende indipendente il Data Flow
dalla sorgente dati.
Metadati
I Metadati sono dati che descrivono altri
dati.
SSIS è basato sui metadati.
Grazie ai metadati è possibile validare il
package a priori.
Però è abbastanza laborioso cambiare i
tipi di dati in input.
Connections
25
Data source
Data source views
Connection Managers
26
Data source
E’ un riferimento ad una connessione
creato al di fuori dei package
Quando si crea un Data Source:
BIDS aggiunge un connection manager (OLE
DB)
La proprietà DataSourceID di questo
connection manager punta al data source.
Più package vi possono fare riferimento.
Semplifica l’aggiornamento
27
Data Source View
Descrive lo schema di un data source.
Rende disponibili gli oggetti di un
database che possono poi essere utilizzati
per definire source o destination
E’ estendibile:
Posso creare campi calcolati
Posso introdurre filtri
Posso sostituire tabelle con named query.
Non appartiene ad uno specifico package.
28
Connection Manager
Rappresentazione logica di una connessione
In design time si imposta la proprietà ConnectionString.
Al runtime SSIS crea una connessione basandosi su
questa proprietà.
Tipo
Descrizione
ADO
Connects to ActiveX Data Objects (ADO) objects
ADO.NET
Connects to a data source by using a .NET provider.
EXCEL
Connects to an Excel workbook file.
FILE
Connects to a file or a folder.
FLATFILE
Connect to data in a single flat file.
FTP
Connect to an FTP server.
HTTP
Connects to a web server.
MSMQ
Connects to a message queue.
MSOLAP90
Connects to an instance of SQL Server 2005 Analysis Services (SSAS) or an Analysis Services
project.
MULTIFILE
Connects to multiple files and folders.
MULTIFLATFILE
Connects to multiple data files and folders.
OLEDB
Connects to a data source by using an OLE DB provider.
ODBC
Connects to a data source by using ODBC.
SMOServer
Connects to a SQL Server Management Objects (SMO) server.
SMTP
Connects to an SMTP mail server.
SQLMOBILE
Connects to a SQL Server Compact Edition database.
WMI
Connects to a server and specifies the scope of Windows Management Instrumentation (WMI)
management on the server.
29
EXCEL
E’ utilizzato per Microsoft Office Excel
2003 o precedenti.
Per Excel 2007 occorre utilizzare:
Microsoft Office 12.0 Access Database
Engine OLE DB Provider
Bisogna impostare: Extended
Properties = Excel 12.0
30