SQL Server 2005Integration Services

Franco Perduca Factory Software

francop@factorysw.com

Introduzione

Cosa sono i SSIS?UtilizziArchitetturaVersioniSSIS 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:

ExtractTransformLoad

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 packageTask per le funzionalità di workflowData source e data destinationTask per la trasformazione, aggregazione, copia dei dati

4

Quando li utilizziamo?Ecco alcuni scenari di utilizzo:

Integrazione di dati eterogeneiPopolamento di datawarehouse e datamartPopolamento di fact table e dimensioniSlowly changing dimension taskPulizia e standardizzazione dei datiLe funzionalità di trasformazione (anche via script) sono strumenti potenti per questo scopoAutomatizzazione di funzionalità amministrativeBackup/restore, copia di oggetti di SQL Server, process di cubi OLAP

5

Architettura di Integration Services

SSIS ServiceStart e stop di packageMonitoraggio Gestione package storage

SSIS runtime engineEsecuzione packageSupporto perLoggingDebugConfigurazione

SSIS data flow engineGestione buffer per il movimento dati da source a destinationGestione trasformazioni

SSIS clients (object model)

Tool grafici e da command lineAPI

6

Tools

SviluppoVisual studio 2005Business intelligence development studio

Amministazione/EsecuzioneSQL Server Management StudioDTUtilDTExec

7

SSIS e versioni SQL Server 2005

SQL ExpressSSIS non è disponibile per questa versione

WorkgroupSolo import/export wizard

StandardMancano solo le funzionalità avanzate di Business intelligence

Enterprise/DeveloperVersione 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.exeAlcuni driver non sono disponibili, ad es: Microsoft OLE DB Provider for JetProblema con Export su ExcelNon è possibile disegnare package su ItaniumGli script devono essere precompilatiNon è possibile utilizzare il task Execute DTS 2000 Package. Run64bitRuntime = False in Project Properties se si vuol eseguire il package con il runtime a 32bitIl 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

PackageControl flowContainerTaskVariable

10

PackageIl package è un oggetto contenente una collection di oggetti interrelati.

11

Package

E’ il punto di partenza per lo sviluppo di SSISAl 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 ContainerTask

13

Container

Organizzano il package in unità più piccole Forniscono :

Transaction scopeVariable scopeExecution scopeLooping functionsBreakpointsError routingLogging scope. Il package stesso è un container.

14

Container

For Each loopEsegue il workflow una volta per ciscun elemento di una collection

For loopEsegue il workflow finchè una condizione non diventi falsa.

Sequence containerUnità di organizzazione dei task

TaskHostContenier di ciascun taskTrasparente all’utente

15

Task

Eseguono il lavoroOgni task ha un compito specificoDataFlow Task:

Sposta i datiTrasforma di dati

Sono estensioni “pluggable”Possono essere sviluppati da terze parti

16

Variables

Gli oggetti di Integration Services sono isolati, non “vedono” gli altri oggettiLe varibili sono utilizzate come meccanismo di comunicazione tra gli oggetti.Possono contenere numerosi datatype:

Stringhe NumeriDateDatasetOggetti17

Variables

Possono avere scope diversi:PackageContainerTask

Aiutano a rendere i package più configurabili e flessibili.Esistono numerose variabili di sistema

18

Connection ManagersForniscono un link al mondo estero a SSISAlcuni 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:

OLEDBFileSQL ServerAnalysis ServicesOracleXMLSAPHTTP

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 HANDLERSSe 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

22

Evento DescrizioneOnError This event is raised by an executable when an error occurs.OnExecStatusChanged This event is raised by an executable when its execution status changes.

OnInformation This event is raised during the validation and execution of an executable to report information. This event conveys information only, no errors or warnings.

OnPostExecute 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.

OnProgress This event is raised by an executable when measurable progress is made by the executable.

OnQueryCancel This event is raised by an executable to determine whether it should stop running.

OnTaskFailed This event is raised by a task when it fails.

OnVariableValueChanged

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 .

OnWarning 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.

OnInformation This event is raised during the validation and execution of an executable to report information. This event conveys information only, no errors or warnings.

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 sourceData source viewsConnection Managers

26

Data source

E’ un riferimento ad una connessione creato al di fuori dei packageQuando 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 destinationE’ estendibile:

Posso creare campi calcolatiPosso introdurre filtriPosso sostituire tabelle con named query.

Non appartiene ad uno specifico package.28

Connection Manager

Rappresentazione logica di una connessioneIn design time si imposta la proprietà ConnectionString.Al runtime SSIS crea una connessione basandosi su questa proprietà.

29

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.

MSOLAP90Connects 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.

WMIConnects to a server and specifies the scope of Windows Management Instrumentation (WMI) management on the server.

EXCEL

E’ utilizzato per Microsoft Office Excel 2003 o precedenti.Per Excel 2007 occorre utilizzare:

Microsoft Office 12.0 Access Database Engine OLE DB ProviderBisogna impostare: Extended Properties = Excel 12.0

30