Alessandro Alpi's blog

[SSMS] La gestione dei template

2008-04-06T15.27.00Z

SQL Server Management Studio ci offre la possibilità di utilizzare dei template predefiniti per la creazione e la modifica di oggetti. La lista dei template, accessibile dal Template Explorer, è veramente ampia. Eccone un'anteprima:

Come possiamo vedere vi sono molti modelli disponibili. Si possono modificare, se ne possono creare di nuovi. Vi è un'ampia libertà di movimento. In aggiunta abbiamo anche una lista dei template recentemente utilizzati dalla quale pescare i nostri preferiti.
Ma oltre alla lista, esiste la possibilità di scriverli "al volo" per utilizzarli subito, ad esempio, all'interno di uno script SQL. Prendendo spunto da uno dei tanti template disponibili sul Template explorer, notiamo il formato dei placeholder:

<nome_parametro, tipo_dati, valore>

nome_parametro rappresenta il nome del parametro nello script.
tipo_dati è il tipo di dati del parametro.
valore è il valore che sostituirà ogni occorrenza del parametro nello script.

Facciamo un semplice esempio. Ipotizziamo di dover creare uno script atto alla creazione di un database.
Vogliamo che il database sia formato da tre file (un mdf, un ldf ed un ndf) i cui nomi sono composti dal nome del database seguito da un suffisso, e da due FILEGROUP, PRIMARY e SECONDARY. Ecco un possible script, che va bene per ogni database:

CREATE DATABASE <NomeDB, stringa, > ON PRIMARY
( NAME = N'<NomeDB, stringa, >_data', FILENAME = N'C:\Databases\<NomeDB, stringa, >_data.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ),
FILEGROUP [SECONDARY]
( NAME = N'<NomeDB, stringa, >_user', FILENAME = N'C:\Databases\<NomeDB, stringa, >_user.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'<NomeDB, stringa, >_log', FILENAME = N'C:\Databases\<NomeDB, stringa, >_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'<NomeDB, stringa, >', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC <NomeDB, stringa, >.[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE <NomeDB, stringa, > SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE <NomeDB, stringa, > SET ANSI_NULLS OFF
GO
ALTER DATABASE <NomeDB, stringa, > SET ANSI_PADDING OFF
GO
ALTER DATABASE <NomeDB, stringa, > SET ANSI_WARNINGS OFF
GO
ALTER DATABASE <NomeDB, stringa, > SET ARITHABORT OFF
GO
ALTER DATABASE <NomeDB, stringa, > SET AUTO_CLOSE OFF
GO
ALTER DATABASE <NomeDB, stringa, > SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE <NomeDB, stringa, > SET AUTO_SHRINK OFF
GO
ALTER DATABASE <NomeDB, stringa, > SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE <NomeDB, stringa, > SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE <NomeDB, stringa, > SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE <NomeDB, stringa, > SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE <NomeDB, stringa, > SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE <NomeDB, stringa, > SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE <NomeDB, stringa, > SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE <NomeDB, stringa, > SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE <NomeDB, stringa, > SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE <NomeDB, stringa, > SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE <NomeDB, stringa, > SET READ_WRITE
GO
ALTER DATABASE <NomeDB, stringa, > SET RECOVERY FULL
GO
ALTER DATABASE <NomeDB, stringa, > SET MULTI_USER
GO
ALTER DATABASE <NomeDB, stringa, > SET PAGE_VERIFY CHECKSUM
GO
USE <NomeDB, stringa, >
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'SECONDARY') ALTER DATABASE <NomeDB, stringa, > MODIFY FILEGROUP [SECONDARY] DEFAULT
GO

Si puo notare che al posto di ogni nome database esiste un placeholder così formato:

<NomeDB, stringa, >

Come possiamo usarlo? Come lo sostituiamo? Innanzitutto fate attenzione ai percorsi e se non avete le cartelle, createle a priori, altrimenti avrete un errore.

1) Copiate lo script su management studio in una nuova query.
2) Premete CTRL+MIUSC+M (ho la versione in inglese di SSMS, quindi non garantisco sullo shortcut) oppure aprite il menu Query --> Specify Values for Template Parameters..
3) Riempite la form che vi appare con i valori desiderati:

Come potete notare la form riassume proprio la struttura definita nel placeholder <NomeDB, stringa, >.

4) Date l'ok ed eseguite la query.

Una volta creato il vostro template, potete aggiungerlo a quelli predefiniti di SQL Server, creando un nuovo modello direttamente dal template explorer.

Una volta creato, è sufficiente premere il destro sul template e selezionare Edit, creare il modello e salvare.
Nel caso in cui si debba reinstallare il client o aggiornare la versione, ricordate che i template sono accessibili Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\Templates (SQL SERVER 2005 su sistema operativo in INGLESE, in italiano la cartella è Dati Aplicazioni).

Stay Tuned

[SSIS] Articolo sui SSIS Log Providers

2008-04-03T15.45.00Z

Grazie all'impegno di David, che ho stressato molto per la pubblicazione di alcuni miei articoli, è online "Il logging con Integration Services".

"Perchè si è bloccata la mia applicazione? Che errori si sono verificati? Perchè il flusso d'esecuzione non procede nel modo corretto? Cerchiamo di capire come possono esserci utili le funzionalità di logging offerte da SQL Server e gli Integration Services per rendere più affidabili e consistenti le nostre applicazioni".

Aspetto commenti e critiche .. spero possa comunque esservi di aiuto.

Stay Tuned!

[SSIS] I Checkpoint - Workaround Foreach Loop

2008-03-18T20.42.00Z

In questo mio post, definivo le caratteristiche dei checkpoint ed un'introduzione alla funzionalità di SSIS.

Vi sono casi particolari, come quello indicato in questo thread su microsoft.public.it.sql, che necessitano di alcuni workaround per portare dalla propria parte lo svantaggio che il For each Loop container ha con i checkpoint . Nel thread indicato, vi è una semplice richiesta. Ottenere un Sequence container che, in caso di errore, non rispetti le semplici condizioni di checkpoint e che quindi si riavvii per intero. Comportandosi naturalmente, al contrario, il task che ha generato l'errore all'interno del contenitore è quello che verrà eseguito per primo al riavvio, ignorando i precedenti.

Il workaround è semplice:

- impostare la proprietà FailPackageOnFailure a false in TUTTI i task interni ai sequence container
- Aggiungere in ogni sequence container un Foreach Loop container
- Impostare il Foreach Loop container con l'enumeratore "Item"
- Aggiungere sotto una colonna di tipo intero ad esempio (un item con un qualunque valore)
- Impostare la proprietà FailPackageOnFailure del Foreach Loop a true
- Spostare tutti i task che erano nel sequence container all'interno del Foreach Loop container
- Impostare la proprietà FailParentOnFailure di ogni task (di primo livello) interno al Loop a true
- Imposta la proprietà CheckpointUsage del package a IfExists e la FailPackageOnFailure del package a false (con CheckpointFileName = "path/file.chk" e SaveCheckpoints = true)

La chiave è quella di sfruttare al meglio lo svantaggio del foreach

In materia di checkpoint (e non solo), consiglio un video di Brian Knight, su jumpstartTV. Non fatevi ingannare dalla signora prima del video di Brian, è solo pubblicità; non c'entra con SQL Server

Stay tuned!

[SSIS] I Checkpoint

2008-03-18T20.38.00Z

SSIS 2005 possiede una importante funzionalità, quella dei Checkpoint. Ogni pacchetto, opportunamente configurato, può creare e gestire un checkpoint file, in grado di memorizzare gli stati del package a runtime. Detto in parole povere, il checkpoint consente di ripristinare una situazione corrispondente al verificarsi di un errore.
Ogni volta che si verifica un errore il file di checkpoint viene scritto ed un particolare record in formato xml viene accodato agli eventuali precedenti. Una volta che il pacchetto termina l'esecuzione con successo cancella il file di checkpoint, per ripristinare lo stato iniziale di esecuzione.

Ovviamente una tecnologia semplice come questa può risolverci alcuni problemi ed alleggerire in generale il carico dei server o della rete inclusa nella logica di importazione/trasformazione.

Quali sono i vantaggi dei checkpoint?
- Come già detto, evitare di ripetere alcune operazioni. Immaginiamo infatti particolari inserimenti o modifiche che non devono essere ripetuti. In questo caso possiamo evitare di scrivere logiche di controllo potenzialmente pesanti.
- Evitare di ricaricare grandi quantità di dati, riducendo l'eventuale carico di rete congiunto
- Evitare di ricaricare file di grandi dimensioni, riducendo anche qui il carico di rete
- Evitare di ripetere eventuali aggregazioni

Il record scritto nel file di checkpoint contiene anche i valori correnti delle variabili, escluse quelle reference type (object).
L'unità più piccola che può essere riavviata è il task (o meglio l'host container invisibile che lo contiene). Anche alcuni contenitori come il Sequence Container sono considerati come unità riavviabili. Mentre però in un Sequence Container il task che ha generato l'errore dirige il riavvio, è necessario fare particolare attenzione al For each loop container. Infatti, con un errore, la riesecuzione riparte da zero e quindi l'enumeratore riparte dal primo elemento della collezione ciclata. Non è possibile riprendere da una particolare iterazione. Questo può essere un comportamento non voluto e quindi uno degli svantaggi più evidenti del Foreach loop container. Immaginate ad esempio un ciclo foreach che esegue n transazioni (ad esempio n inserimenti). In caso di errore durante un inserimento diverso dal primo il package è pensato per scrivere sul checkpoint che il ciclo è andato in errore. La riesecuzione forza tutti i cicli del container come "da rieseguire", ripartendo dal primo. Questo può portare un vero e proprio errore anche sulla qualità del dato.

Ci sono tre variabili delle quali tenere conto durante l'impostazione di un package per supportare i checkpoint:
- CheckpointFileName
- CheckpointUsage
- SaveCheckpoints

La prima definisce il nome del file per il checkpoint (non vi è la possibilità di impostare un connection manager, ma per rendere dinamico il percorso è sufficiente definire un'Expression che imposta il nome stesso). La seconda è un enum che definisce qual è l'utilizzo del checkpoint file e la terza, se valorizzata a true, impone il salvataggio del checkpoint per ripartire dall'ultimo errore incontrato.

CheckpointUsage può assumere a sua volta tre valori:
- Never, Il package non utilizza il file dei checkpoint
- Always, Il package usa sempre il file del chackpoint e se non lo trova restituisce errore
- IfExists, Il package utilizza il file se esiste. In caso contrario prosegue l'esecuzione

Altra proprietà da utilizzare in concomitanza con la definizione dei checkpoint è la FailPackageOnFailure. Questa proprietà, se valorizzata a true ed in caso di errore del task che la possiede, scatena l'evento per la scrittura del checkpoint sul file.

Ecco come si presenta un file di checkpoint:

<DTS:Checkpoint xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:PackageID="{CF9B140D-67D3-4D86-AF08-87C2C1DCBA3A}">
      <DTS:Variables DTS:ContID="{CF9B140D-67D3-4D86-AF08-87C2C1DCBA3A}">
            <DTS:Variable>
                  <DTS:Property DTS:Name="Expression"></DTS:Property>
                  <DTS:Property DTS:Name="EvaluateAsExpression">0</DTS:Property>
                  <DTS:Property DTS:Name="Namespace">User</DTS:Property>
                  <DTS:Property DTS:Name="ReadOnly">0</DTS:Property>
                  <DTS:Property DTS:Name="RaiseChangedEvent">0</DTS:Property>
                  <DTS:VariableValue DTS:DataType="3">0</DTS:VariableValue>
                  <DTS:Property DTS:Name="ObjectName">var1</DTS:Property>
                  <DTS:Property DTS:Name="DTSID">{8758E0D8-E786-4497-BE6F-8146F9D6FBA3}</DTS:Property>
                  <DTS:Property DTS:Name="Description"></DTS:Property>
                  <DTS:Property DTS:Name="CreationName"></DTS:Property>
            </DTS:Variable>
      </DTS:Variables>
      <DTS:Container DTS:ContID="{005F0094-ED1D-41C5-88BF-F6E6E13D1BBF}" DTS:Result="0" DTS:PrecedenceMap=""/>
</DTS:Checkpoint>

Si tratta di un XML file che contiene le informazioni leggibili dei task che sono andati in errore. Molto semplice da leggere e da capire. Una sezione di intestazione del checkpoint, un eventuale elenco di variabili e le relative proprietà, il container di riferimento, ecc.

Ho trovato questa funzionalità estremamente utile nei casi di scratch load. Poter fermare il package in caso di errore mentre si stanno caricando grandi quantità di dati da enormi file di testo, con la garanzia di poter ripartire da un determinato punto, è del tutto vantaggioso.

Stay tuned!

[SSIS] Dataflow - Le trasformazioni - Conditional Split

2008-02-24T16.55.00Z

Abbiamo già accennato in questo post l'utilizzo del Conditional Split transform per saltare le righe in un determinato flusso di input. Le principali caratteristiche sono quindi già state evidenziate, e sono:
- In quanto trasformazione, sta all'interno di un dataflow e non può essere il primo task della trasformazione stessa.
- In quanto trasformazione, possiede un input ed n output (uno per condizione creata più uno per la condizione di default)
- In input riceve quello che arriva da un task precedente e lo gestisce in base alle condizioni create
- Possiamo indicare nelle espressioni di condizione sia variabili che colonne

In aggiunta ad esse vi è l'advanced editor anche se le proprietà avanzate non sono molto importanti. Diciamo che l'editor di default è più che sufficiente per configurare il componente al meglio.
Solo un'opzione, la ValidateExternalMetadata, può esserci di aiuto nell'editor avanzato. Essa serve infatti per decidere se validare i metadati esterni al componente a design time:

IDTSComponentMetaData90.ValidateExternalMetadata Property

Può essere utile, ad esempio, se le condizioni del componente sono create a runtime e quindi per quelle condizioni i cui metadatai ancora non esistono. Lasciando a true la proprietà ValidateExternalMetadata in un caso come questo, otterremmo un errore in fase di design, il che impedirebbe il corretto caricamento del pacchetto in fase di editing e, a volte, anche di esecuzione. Ricordiamo che questa proprietà non è riservata al task Conditional Split, bensì a tutti i task di trasformazione di SSIS (tutti i task di un dataflow).

Per dare una ulteriore overview del task, vediamo come esso può risultare utile per la gestione dei NULL.

Scenario
Partendo da un foglio excel contenente i contatti di una rubrica, dobbiamo caricare questi dati su SQL Server 2005. Il campo email sul foglio excel può essere non riempito ma su SQL Server non è consentito l'inserimento di NULL. Se la email risulta mancante, si dovrà inserire su sql server il valore stringa "N/A".

Ecco come procedere:

1) creare un connection manager per i contatti
2) creare un dataflow che conterrà la trasformazione
3) aggiungere una sorgente Excel e definire il record
4) legare all'output della sorgente il conditional split per gestire i NULL
5) mettere unione i dati non NULL con quelli NULL gestiti dal conditional split
6) inserire su tabella SQL Server

1) Per creare il connection manager dobbiamo aggiungerne uno non presente nel menu rapido. Dovremo utilizzare New Connection..:

Ho spuntato il flag proprio perchè la prima riga del mio excel ha i nomi delle colonne. Ricordarsi di deselezionarlo nel caso contrario
Dopo aver confermato, ho rinominato il connection manager in Contatti.

3) Ecco i dati in anteprima ottenuti legando il connection manager alla sorgente Excel:

Come possiamo notare i dati con id 5,6,7,8,9 non hanno la mail valorizzata e l'informazione è NULL.

4) Ecco come gestire il null nella conditional split:

Avremo in output due rami, uno, che per noi è quello corretto, con le email valorizzate, l'altro, da gestire poichè la colonna email è NULL. Possiamo ad esempio aggiungere un derived column per inserire nel valore NULL la stringa "N/A":

A questo punto siamo pronti ad unire i risultati per ottenere un resultset "pulito" e pronto da inserire su SQL Server.

5) Utilizzando il task Union All andiamo ad unire i dati gestiti dal Derived Column con quelli già "puliti" uscenti dalla condizione di default del Conditional Split:

Come possiamo vedere dal primo data viewer (ricordiamo che un dataviewer è un insieme di informazioni che ci consentono di eseguire debug e stime sui dati) abbiamo i dati gestiti dal Derived Column "Stringa Not Available". Nel secondo abbiamo i dati uscenti dal Conditional Split "email NULL" mentre nel terzo abbiamo l'unione dei due resultset in uno solo. Questi ultimi sono i dati che effettivamente saranno inseriti in SQL Server.

Stay Tuned!

[SSIS] Qualche problema con SP2

2008-02-11T10.14.00Z

L'altro giorno ho riscontrato un problema noioso sul service pack 2 di sql server per quanto riguarda Integration Services.

Scenario
Sistema cluster windows 2003, failover di SQL Server 2005 a 64bit. Installazione nuovo ambiente, e quindi installazione SQL Server in cluster, installazione dei client tools, di SSIS e di VSBIDS (Business Intelligence Development Studio). Successivamente, Service Pack 2, per tutto.

Ebbene, dopo l'installazione di SP2, sembra andare tutto bene fino a che mi accorgo che Visual Studio BIDS non funziona più.. Non riesco a creare package nuovi, non riesco a fare debug di già esistenti, ed ottengo errori di vario genere, come ad esempio:

"Microsoft Visual Studio is unable to load this document:
The package failed to load due to error 0xC0010014 “One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.”. This occurs when CPackage::LoadFormXML fails."

Partendo da questo messaggio non ho trovato nulla che potesse aiutarmi.
Davide, non conoscendo bene il problema che avevo di fronte, mi ha passato un paio di link, che purtroppo non erano adatti al mio scopo..

Il week end è passato e questa mattina mi arriva una email simpatica di Leo, un mio "amico/collega/cliente" che mi insulta un po' e mi dona questo link:

http://support.microsoft.com/kb/919224

CAUSE

loadTOCNode(1, 'cause');"This problem occurs because the installer unregisters the 32-bit Dts.dll file when the installer installs the 64-bit Dts.dll file. The 64-bit Dts.dll file is installed together with SQL Server 2005 SP2. Additionally, the 64-bit Dts.dll file may be installed together with any SSIS hotfix package that contains the 64-bit Dts.dll file."

Semplicemente risolto con il metodo indicato..
Comunque un "bel" bug.. voi che ne pensate?

Stay tuned!

[SSIS] Dataflow - Le trasformazioni - Sort e Aggregate

2008-01-19T18.43.00Z

Proseguiamo con le trasformazioni, ormai mancano pochi post . Poi passeremo alla versione 2008 per capire anche come cambiano i componenti. Come abbiamo già visto in questo post il Lookup component ha migliorato di molto la gestione della cache e del match. Alcuni componenti rimarranno simili ai predecessori, ma in linea di massima, essendo SSIS 2005 un prodotto riscritto interamente, troveremo tante migliorie e comodità in più.
Ma torniamo a noi. Parleremo di due semplici trasformazioni, il Sort Transformation e l'Aggregation Transformation.

Sort Transformation
Come già il nome indica il sort è un componente in grado di ordinare i dati. Riceve quindi in input un resultset e lo restituisce ordinato in base ai criteri definiti al suo interno. In base all'ordine con cui sono state selezionate le colonne viene effettuato l'ordinamento. Se si selezionano tre colonne, l'ordinamente viene effettuato prima per la prima selezionata, poi per la seconda ed infine per la terza. Inoltre vi è una funzionalità alternativa che permette di rimuovere le righe duplicate in base alle colonne selezionate per l'ordinamento. Questo componente risulta molto utile quando è necessario eseguire una Merge Join (ne parleremo nei prossimi post). Quest'ultimo infatti, si aspetta in input resultset ordinati. Prendiamo il seguente esempio:

Scenario

Due server di natura differente SRV01 e SRV02. Nel primo ho un'anagrafica di utenti, nel secondo ho i dettagli dell'utente che non sono gestiti nelle applicazioni del primo server. Ho la necessità di collegare gli utenti con i relativi dettagli per poter scrivere su di una tabella presente su SRV01. Immaginiamo un dataflow simile a questo:

Siccome dobbiamo legare le due sorgenti provenienti da server differenti, possiamo utilizzare il task Merge Join. Rimuovendo il sort si ottiene un errore restituito dal package a design time:

"The IsSorted Property must be set to true on both sources of this transformation"

Anche aggiungendo l'order by nel comando sorgente il problema rimane. Il sort transformation risolve questo problema. A dire il vero, questo non è l'unico metodo.
In questo tip infatti vi è la procedura per impostare la proprietà IsSorted a True. Dategli una letta

Abbiamo visto che il sort ordina i resultset. Ora vediamo com'è nel dettaglio:

E molto semplice ed intuitivo, ha una sola form tramite la quale è possibile selezionare tre opzioni. Il primo flag a sinistra seleziona la colonna o le colonne che devono essere ordinate. Il secondo flag, denominato Pass Through serve per determinare se "inoltrare" la colonna nell'output. Quando il checkbox è deselezionato, l'output non possiede la colonna relativa.
L'ultima opzione, di fianco ai tasti di conferma, permette di eseguire la distinct sulle colonne selezionate, rimuovendo i duplicati in base alle colonne scelte per l'ordinamento.

Come la maggior parte dei task, anche il Sort ha un "Advanced Editor", raggiungibile premendo il tasto destro sul componente.
Qui troverete tutte le opzioni, comprese quelle che nei designer non vengono proposte.

Aggregate Transformation
Il task si occupa di aggregare i dati. Lo fa tramite funzioni di aggregazione come la AVG, o la SUM, oppure tramite semplici GROUP BY di dati. Può essere utilizzato in Basic Mode oppure in Advanced Mode. Nel primo caso possiamo definire trasformazioni a livello di colonna. Mentre nel secondo è possibile definre regole per il controllo della cache e delle prestazioni, nonchè la definizione di output multipli.
L'aggregation transformation è del tutto asincrono, quindi non esegue i raggruppamenti row-by-row, ma prepara una parte di aggragazioni creando le colonne in uscita, ragionando set based ed utilizzando una sua cache.

Ecco come appare in Basic Mode:

Quelle nell'immagine sono tutte le funzioni di aggregazione possibili. Ma non tutti i tipi dato possono disporre dell'intero insieme. Leggete qui per capire quali funzioni sono disponibili in base al tipo di dato. Inoltre fate attenzione per alcuni tipi di dato numerici, dai BOL:

"A column may contain numeric values that require special consideration because of their large value or precision requirements. The Aggregation transformation includes the IsBig property, which you can set on output columns to invoke special handling of big or high-precision numbers. If a column value may exceed 4 billion or a precision beyond a float data type is required, IsBig should be set to 1.

Setting the IsBig property to 1 affects the output of the aggregation transformation in the following ways:
    * The DT_R8 data type is used instead of the DT_R4 data type.
    * Count results are stored as the DT_UI8 data type.
    * Distinct count results are stored as the DT_UI4 data type.

Note:
You cannot set IsBig to 1 on columns that are used in the GROUP BY, Maximum, or Minimum operations."

Per impostare la Proprietà IsBig è necessario utilizzare l'advanced editor (tasto destro --> Advanced editor, sul task) e spostarsi sul tab "Input And Output". Selezionando una delle colonne di output che interessa la modifica è possibile impostare la proprietà.

Nel basic mode quindi, è sufficiente selezionare le colonne e definire la funzione di aggregazione. Avremo un output con gli aggregati.

In Advanced Mode invece, abbiamo quanto segue:

La sezione superiore indica la possibilità di definire più output per la trasformazione. Immaginiamo di voler tornare il numero dei dettagli per utente in un resultset e la semplice group by degli utenti in un altro. Con l'advanced mode è possibile creare resultset multipli definiti in maniera differente.

Spostandosi sul tab Advanced è possibile modificare le proprietà relative all'ottimizziazione delle performance.
Number of Keys consente di specificare il numero esatto di chiavi per la trasformazione.
KeysScale consente di specificare un numero di chiavi approssimativo.
Valorizzando Number of Keys, la trasformazione non riscrive i dati nella cache, permettendo un buon miglioramento delle prestazioni.
Lo stesso discorso vale per le proprietà CountDistinct solo che coinvolgono solamente le operazioni di quel tipo (COUNT DISTINCT).
Infine vi è la proprietà Auto Extend factor, che determina la percentuale del'estensione della memoria utilizzata durante le operazioni di aggregazione.

Modificando opportunamente questi valori (ovviamente in base a quanto si conosce dell'operazione di aggregazione) è possibile gestire memoria e cache in modo tale da migliorare le prestazioni a runtime.

Abbiamo visto altri due task di trasformazione, nei prossimi post parleremo anche del precedentemente nominato Merge Join.

Stay tuned!

[SSIS 2008] Primi passi.. I miglioramenti del lookup transform

2008-01-11T10.09.00Z

Ho finalmente iniziato a guardare SSIS 2008, anche se forse avrei già dovuto farlo da un po'..
Spulciando tra i vari blogger del settore ho notato questo interessante post di Jamie Thomson uno degli esperti nel settore.
La sua pubblicazione è una semplice overview che parla del famoso task Lookup, del quale ho parlato più volte in alcuni post precedenti.
Bisogna già conoscere il Lookup Transform della versione 2005, se volete dare una lookup andate qui.

Aveva di sicuro alcuni vuoti da colmare, e, come detto nel messaggio di Jamie, alcuni aspetti sono stati veramente migliorati in maniera evidente.
In breve, abbiamo una ristudiata e riscritta gestione della cache, utilizzata ora anche per sorgenti come file di testo, XML, Excel, e quant'altro.
Poi, finalmente, abbiamo la gestione dei dati che non fanno "match" tramite un nuovo apposito output chiamato Lookup No Match Output(ricordiamo che con SSIS 2005 eravamo costretti a controllare l'error output per controllare quali dati non rispettavano il match sulla tabella di lookup).

Vi invito a guardare il post di Jamie, è compreso anche un video interessante..

Stay Tuned!

[OT] - Auguri di Buone Feste

2007-12-23T12.07.00Z

Auguri di Buon Natale e Felice Anno nuovo a tutti!
e come sempre..
Stay Tuned!

[SSIS] - Il bypass del Web Service Task con uno script task

2007-12-19T18.25.00Z

Con SQL Server Integration Services è possibile utilizzare un particolare task, chiamato Web service Task, utilizzato proprio per invocare Web services. E' il seguente:

Prendiamo un semplicissimo scenario, il web service di default che Visual Studio 2005 ci propone quando creiamo un progetto Web Service. Il classico Hello World.
L'utilizzo del task è molto semplice. Gli step da seguire per poter usufruire delle sue funzionalità sono i seguenti:

- Creare un connection Manager di tipo HTTP

Nell'esempio ho impostato semplicemente il link http al mio WSDL di prova, ma come potete notare ci sono altre impostazioni molto importanti da poter utilizzare.
Ad esempio è possibile fornire username, password e dominio di accesso, impostare un certificato, definire il timeout, la dimensione del blocco per la scrittura dei dati e testare la connessione.
In alto vi è anche la clip dedicata alle impostazioni di un eventuale proxy.
Siccome questa connessione HTTP è stata appositamente creata per un web service task e siccome avrò bisogno di conoscere la struttura del servizio, e quindi un WSDL, l'url di riferimento comprende il parametro ?WSDL. Chiamiamolo Servizio

- Creare una variabile che conterrà la risposta (o un connection manager File per scrivere la risposta su filesystem)

E' possibile ad esempio creare una variabile di tipo stringa che conterrà la risposta del nostro servizio.

- Creare nella cartella del nostro progetto o su di una posizione nel disco ben definita un file vuoto con estensione .wsdl

- Impostare il Web Service Task come segue:

Fare doppio click sul Web service Task.
Come prima cosa selezionare la http connection che abbiamo creato pocanzi e poi definire nella proprietà WSDLFile il percorso ove il file .wsdl è stato creato.
La terza proprietà, OverwriteWSDLFile, indica se si vuole sovrascrivere il file alla pressione del tasto sottostante, il Download WSDL.
La prima volta, ed ogni volta che la struttura del servizio cambia (e quindi ogni volta che si vuole scaricare il nuovo WSDL) lasciare questa proprietà impostata a True.
A questo punto, premere il tasto Download WSDL e attendere il messaggio di conferma:

Se la proprietà OverwriteWSDLFile è a False, viene restituito un messaggio di errore.
Facoltativamente, potete andare a controllare sul disco e noterete che il file scaricato è effettivamente il WSDL del servizio che dobbiamo invocare.

- Definire la sezione Input

Spostarsi sulla sezione input. Scegliere il servizio (proprietà Service) ed il metodo da eseguire (proprietà Method). Nel caso in cui il metodo abbia parametri, comparirà un'apposita sezione al di sotto della selezione della chiamata. Qui di seguito un esempio di chiamata con un parametro stringa (metodo Message), mentre quello al precedente punto non ne ha alcuno:

Attenzione, perchè per i tipi che non sono considerati primitivi, come possono esserlo interi e stringhe, SSIS non è in grado di fornire il passaggio di parametri. Ad esempio, un metodo definito come il seguente, non restituirà a SSIS nessuna richiesta di parametri input:

<WebMethod()> _
    Public Function NumeroRighe(ByVal ds As DataSet) As Integer
        Return ds.Tables(0).Rows.Count
    End Function

- Definire la sezione Output

Dopo aver configurato l'input, definiamo l'output selezionando il tipo di container della risposta, File o Variabile. Qui di seguito l'esempio su variabile, dichiarata all'inizio del post:

Eseguendo un ipotetico SSIS che lancia il web service suddetto e che in seguito visualizza il valore della risposta avremo questo comportamento:

Dopo aver introdotto il task, vorrei soffermarmi su alcuni ostacoli che potremmo incontrare nell'usarlo. E vorrei anche giustificare il titolo del post .
Oltre a non poter passare parametri di un certo tipo (da MSDN: "The Web Service task supports parameters of the following data types only: primitive types such as integers and strings; arrays and sequences of primitive types; and enumerations."), vi è da ricordare che tutte le risposte del web service task sono stringhe. Ognuna di esse è la serializzazione del valore/oggetto di ritorno. Quindi ogni stringa contiene l'XML della risposta serializzata. Questo perchè il task non si pone di creare una classe proxy come ad esempio Visual Studio fa quando aggiungiamo una Web Reference.
Abbiamo la possibilità di salvare la stringa su un file xml, è vero, ma se volessimo utilizzare la risposta tipizzandola e castandola in un nostro oggetto all'interno di un package, dovremmo per forza ricorrere alla deserializzazione. E quindi scrivere codice.
Di conseguenza, molto spesso non utilizzo il web service task ma direttamente lo script task, in un modo un po' particolare.

Tramite il tool WSDL.exe usato anche da Visual studio per scrivere la classe proxy dal wsdl, genero la classe proxy.

Aggiungo uno script task e includo al suo interno la classe generata con wsdl.exe (con SSIS 2005 solo VB). Inoltre aggiungo il riferimento a System.Web.Services e System.Xml.

Nello ScriptMain possiamo istanziare il manager del servizio (la nostra classe proxy) per eseguire i metodi da codice. Ricordiamo il metodo d'esempio:

<WebMethod()> _
    Public Function NumeroRighe(ByVal ds As DataSet) As Integer
        Return ds.Tables(0).Rows.Count
    End Function

Ecco come nello Script eseguo la chiamata:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

' istanzio il web service
Dim objWS As New Service1()

' creo una tabella con una riga
Dim objdt As New DataTable()

objdt.Columns.Add("col1", GetType(System.String))
Dim objdr As DataRow = objdt.NewRow()

objdr("col1") = "PROVA"
objdt.Rows.Add(objdr)

        ' popolo il dataset
        Dim objDs As New DataSet
        objDs.Tables.Add(objdt)

' chiamo il metodo che mi torna il numero di righe della tabella del dataset
Dim i As Integer = objWS.NumeroRighe(objDs)

' ...

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

Con questo metodo possiamo muoverci con maggiore libertà sui tipi del framework ed anche su quelli definiti da noi. Esiste comunque un rovescio della medaglia, ed è quello relativo alla manutenzione del codice. Cambiando la struttura del web service, è necessario replicare la nuova classe proxy anche negli script che la utilizzano.
ATTENZIONE
Vi è una soluzione più corretta, ovvero creare una dll strongly signed e metterla in GAC, in modo tale da poter aggiungere il riferimento così come per le altre classi .NET. Quest'ultima è sicuramente la soluzione più pulita, se non vogliamo utilizzare il task di SSIS. Ad ogni modo, il riferimento deve essere aggiunto a livello di Script poichè non è possiible referenziare una DLL a livello globale.
Comunque lascio la firma e la creazione dell'assembly in GAC a Voi.. ecco un link utile per capire meglio come firmare la dll
http://msdn2.microsoft.com/en-us/library/ms247123.aspx

Stay tuned!

[SSIS] - I DataFlow - le trasformazioni

2007-12-01T02.32.00Z

Ritorniamo alla nostra allungatissima lezione introduttiva su SSIS , anche se alcuni di voi ormai avranno raggiunto uno skill talmente elevato da non dover nemmeno considerare questa pagina. Ma è il mio progetto iniziale e quindi lo porto a termine, cercando di coprire un po' tutti gli argomenti su Integration Service, perlomento quelli basilari .
L'ultima volta ci eravamo fermati alle destinazioni dei DataFlow. In questo post cominceremo ad affrontare l'argomento più vario della sezione relativa al flusso dati, le Trasformazioni.
Cosa sono? Generalizzando al massimo, mentre le sorgenti sono veri e propri "generatori" di dati e le destinazioni i "contenitori", tutto quello che sta nel mezzo è una trasformazione. Un dataflow può essere formato anche da una o più sorgenti che mappano una o più destinazioni solamente, ma in logiche complesse, i task di trasformazione sono necessari per portare le informazioni dallo stato A in cui si trovano alla sorgente, verso lo stato B necessario alla destinazione. Nella sigla ETL, di cui SSIS rappresenta una delle implementazioni, le trasformazioni sono proprio la T (Extract, Transform and Load).
Per fare semplici esempi, pensate ad alcuni dati che arrivano denormalizzati su txt (in post precedenti, nella sezione SSIS Tips abbiamo avuto modo di vedere già alcuni Dataflow con trasformazioni) e che devono essere portati su strutture più "normali" su sql server, access, excel. O viceversa, dati provenienti da vari RDBMS che necessitano di essere scaricati su file flat, csv, xml, altre piattaforme.. e via discorrendo.

Le trasformazioni possono essere suddivise in quattro macrocategorie:

- Business Intelligence Transformations
Permettono di implementare logiche di business sui dati (ad esempio calcoli di Data Minig).

- Row Transformations
Consentono la creazione e l'aggiornamento di colonne in base alla riga passata in input al task (ad esempio Colonne derivate, Script component, Conversioni di date, Comandi OLEDB per riga)

- Rowset Transformations
Consentono la creazione di set di dati aggregati, ordinati, trasposti (ad esempio il PIVOT/UNPIVOT, i task di raggruppamento, il task sort)

- Split and Join Transformations
Consentono la distribuzione di un input in più output, l'unione di più input in un output oppure eseguono operazioni di lookup (multicast, union e lookup task)

Tutte le trasformazioni che non rientrano in questi quattro gruppi (ad esempio i RowCount, le Slowly Changing Dimension, ecc) sono stati categorizzati da Microsoft come Other Transformations.

Ho deciso di saltare il primo gruppo, quello legato alla BI, soprattutto per frequenza di utilizzo nella maggior parte delle applicazioni in cui i SSIS vengono applicati. Vi lascio comunque la reference su MSDN qualora doveste averne bisogno. L'elenco dei task relativo a questa sezione è:
- Fuzzy Grouping
- Fuzzy Lookup
- Term Extraction
- Term Lookup
- Data Mining Query

Passiamo quindi alle Row transformations. L'elenco è il eguente e anche in questo caso lascio il link ad MSDN:
- Character Map Transformation
- Copy Column Transformation
- Data Conversion Transformation
- Derived Column Transformation
- Script Component
- OLE DB Command Transformation

Le tre maggiormente utilizzate nella mia esperienza da SSIS developer sono state la Derived Column, la Data Conversion e lo Script component.
Con la prima è infatti molto semplice modificare i metadati provenienti da una sorgente. Grazie a Derived Column infatti è possibile aggiungere colonne, indicando un nuovo nome ed una espressione che riassume il valore che conterrà quel particolare campo.
Facciamo un esempio. Supponiamo di avere una sorgente dati che ci passa un campo particolare, che deve essere sia elaborato, sia tenuto com'era in origine. Ad esempio, l'età di una persona e la sua età fra 20 anni (esempio semplificato al massimo, non è necessario un SSIS per questo "problema" ).
In questo contesto possiamo creare una colonna Eta20, derivandola da Eta ed aggiungendo un'espressione data dalla colonna Eta + 20:

Possiamo notare come è semplice creare la colonna derivata, trascinando la colonna in input verso la sezione sottostante per creare la riga su cui fare il calcolo. La prima informazione da inserire è il nome della nuova colonna, il tipo di operazione, l'espressione ed il tipo di dato con le eventuali informazioni aggiuntive sul tipo stesso.
Come tipo di operazione (seconda colonna, Derived Column) è possibile aggiungere una o più nuove colonne, e allora si procede come nell'esempio, oppure sovrascriverne il contenuto. In questo caso l'esempio cambia leggermente. Il nome non è editabile, poichè rimane lo stesso della colonna sorgente, e il tipo di dato dell'espressione deve obbligatoriamente ritornare il tipo della colonna sorgente stessa. Alla fine dell'operazione avremo in output del task un numero di metadati in più tante quante sono le operazioni di <add as new column>.
E' possibile notarlo con un semplice doppio click sui constraints (le frecce):

E' possibile inserire infatti più colonne derivate (nell'esempio solo una), anche solo per convertire il dato.
Tuttavia è consigliabile utilizzare l'altro task progettato proprio per le conversioni, il Data Conversion. Anche questa trasformazione, riceve in input una row e processa i metadati, trasformandoli in nuovi metadati, agendo però solamente sul tipo di dato. Continuiamo col nostro dataflow. Immaginiamo di voler convertire il campo Eta20 da intero a 1byte (DT_I1) verso una stringa:

Si seleziona la colonna e si definisce la conversione. In questo caso, parto dalla colonna Eta20 e arrivo ad una nuova colonna strEta20, di tipo stringa con CodePage 1252 (ANSI Latin) e lunghezza 3 caratteri. Come per la derived column possiamo controllare l'input del task e l'output. Nel nostro caso in input ci sono le tre colonne di output del derived column task ed in output c'è la colonna strEta20 in più.

Seppure la trasformazione Copy Column sia comunque utilizzata, preferisco saltarla; noterete come si avvicina alla Data Conversion (molto più semplice di quest'ultima). Anche la OLEDB Command, che si discosta però da tutte le trasformazioni che stiamo affrontando, non verrà trattata in questo post. E' sufficiente sapere che esegue il comando riga per riga. Quindi prende la riga in input ed esegue un comando SQL. Attenzione a non abusarne, proprio per la sua logica ciclica. Mi è capitato di utilizzarla per gestire alcuni lookup errati, in modo da salvarli all'interno di tabelle di log. Può risultare utile anche per "appoggiare" i dati non validi al momento su di una tabella che viene controllata periodicamente al fine di cercare di ripristinare eventuali record.
Comunque sia vi ho consiglio di leggere la reference su MSDN.

Terminiamo in vece con la trasformazione più particolare, proprio perchè potrebbe anche non essere una vera e propria trasformazione. Lo Script Component. Ogni qual volta esso viene trascinato sullo stage la prima richiesta è "come deve comportarsi?" e le possibili risposte sono "sorgente", "destinazione", "trasformazione". Il caso che ci interessa è proprio quest'ultimo.
Partiamo dal presupposto che anche questo task si comporta come le transformation di cui abbiamo parlato; ha un input (che porta con se metadati) e un output.
Quello che trasforma è la logica dello script, quindi quello che andiamo a scrivere noi. Si utilizza quando altri task non soddisfano le nostre richieste, ma come ho già detto qui e qui, può essere comodo anche per bypassare degli scomodi comportamenti di altri componenti.

Supponiamo, per comodità, di dover ripetere le funzioni definite dai due precedenti task. Partiamo da una tabella che ha il campo Eta e vogliamo arrivare a strEta20. Con lo script, come prima cosa si sceglie la natura del component:

Successivamente si selezionano gli input, ovvero i metadati derivandi dal task precedente:

Da notare la colonna UsageType che determina se le colonne possono essere anche scritte, oltre che lette. La fase seguente consiste nel preparare l'output:

Si rinomina l'output (operazione facoltativa) poi si aggiungono tante colonne quante si vogliono aggiungere in output del task ed infine si definiscono i rispettivi parametri (tipo di dato, lunghezza, codepage, precisione, scala, ecc..). Infine si progetta lo script:

Il metodo già proposto da SSIS è Input0_ProcessInputRow tramite il quale è possibile usare l'oggetto Row, passato come parametro, come se fosse la riga corrente. Ed è proprio in questo metodo che ho scritto la somma e la conversione. Vi sono poi altri metodi su cui è possibile agire:

Per questi leggete questo link relativo ai metodi dello script component.

Mi fermo qui nell'introdurre le trasformazioni. Dobbiamo affrontare ancora due gruppi, e nei prossimi post vedremo come comportarci con le possibilità che Integration Services ci fornisce.
Vedendo una prima carrellata, è normale pensare che lo script potrebbe risolvere molti dei vostri problemi, comunque sia, vi consiglio di capire e conoscere bene gli strumenti e le altre funzionalità che il resto dei task possono darvi. Questo per comprendere se il codice scritto da voi (noi, mi ci metto anche io ) può darvi veramente dei vantaggi.
Ricordatevi che l'architettura di SSIS è ottimizzata ed è in grado di parallelizzare le operazioni (nonchè gestire la memoria in maniera molto intelligente). Quindi fate prima attenzione a tutto quello che potete provare prima di arrivare a scrivere "vagonate" di codice.

Stay tuned!