SQL Server ed ALM su database


Il blog di Alessandro Alpi
Archivio Posts
Anno 2018

Anno 2017

Anno 2016

Anno 2015

Anno 2014

Anno 2013

Anno 2012

Anno 2011

Anno 2010

Anno 2009

Anno 2008

Anno 2007

Anno 2006

[SSIS] Gestire parametri di output di stored procedure

A volte è importante ottenere uno o più dati da stored procedure di SQL Server. Questa risposta si può ottenere scrivendo al loro interno istruzioni SELECT oppure anche valorizzando quei parametri che sono stati definiti come input output. Anche SSIS consente il colloquio con tali tipi di parametro, ma prima, come si definiscono? Qui di seguito un semplice esempio..

Partiamo con la configurazione dell'ambiente. Ipotizziamo di avere una tabella Utenti così formata:

use tempdb;

 

-- Utenti

IF EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id('Utenti'))

      DROP TABLE Utenti

 

-- creazione tabelle

CREATE TABLE Utenti

(

      IDUtente int identity(1,1) NOT NULL,

      Nome varchar(30) NOT NULL,

      Cognome varchar(30) NOT NULL,

      Eta tinyint NOT NULL,

        CodiceFiscale char(16) NOT NULL,

      CONSTRAINT PK_Utenti PRIMARY KEY CLUSTERED

      (

            IDUtente

      )

)

 

-- popolo la tabella Utenti

INSERT Utenti

SELECT 'Alessandro', 'Alpi', 26, 'AAAAAAAAAAAAAAAA'

INSERT Utenti

SELECT 'Michael', 'Denny', 23, 'BBBBBBBBBBBBBBBB'

INSERT Utenti

SELECT 'Marco', 'Rossi', 26, 'CCCCCCCCCCCCCCCC'

INSERT Utenti

SELECT 'Matteo', 'Celaschi', 28, 'DDDDDDDDDDDDDDDD'

INSERT Utenti

SELECT 'Daniele', 'Zanella', 31, 'EEEEEEEEEEEEEEEE

Una volta creata e popolata la tabella, avremo i seguenti record:

-- ecco cosa abbiamo ottenuto

SELECT

      Nome

      ,Cognome

      ,Eta

      ,CodiceFiscale

FROM

      Utenti

ORDER BY

      Cognome

Arrivati a questo punto supponiamo di avere una situazione per la quale risulta necessario sapere l'id restituito ad ogni nuovo inserimento. Può capitare infatti che esso sia necessario per implementare logiche in cascata nel nostro SSIS. Come possiamo ottenere quell'identificativo? Come dicevamo all'inizio, possiamo sfruttare i parametri di output delle stored procedure:

 

CREATE PROCEDURE proc_Utenti

      @Nome varchar(30),

      @Cognome varchar(30),

      @Eta tinyint,

      @CodiceFiscale char(16),

      @IDUtente INT = 0 output

AS

BEGIN

      SET NOCOUNT ON;

      SET XACT_ABORT ON;

 

      BEGIN TRAN

            -- inserisco l'utente nuovo (se non esiste)

            IF NOT EXISTS (SELECT * FROM Utenti WHERE CodiceFiscale = @CodiceFiscale)

            INSERT Utenti

            SELECT @Nome, @Cognome, @Eta, @CodiceFiscale

           

            SET @IDUtente = SCOPE_IDENTITY()

 

      COMMIT TRAN

END

Questa procedura ci consente di inserire l'utente e di ottenere l'ultimo identity inserito nella tabella Utenti se effettivamente l'inserimento si è verificato. In caso opposto, il parametro varrà 0, così come indicato nel default del parametro. Ma ora ci rimane da capire come SSIS può ottenere quel valore..

Il tutto è molto semplice. Tralasciando la spiegazione della sorgente e della destinazione del dataflow che andrò a illustrare, mi sposto invece sulla coppia di task utili ad implementare la logica del mapping del parametro di output; Derived Column e OLEDB Command:

Derived Column

Aggiungiamo una colonna derivata col valore di inizializzazione che più crediamo opportuno. Chiamiamola IDUtenteOUT

 

OLEDB Command

Sottolineata in blu la chiamata da effettuare, indicando la stringa OUTPUT nel parametro che ci tornerà il valore. Il connection manager utilizzato è un OLEDB verso tempdb, dove abbiamo creato gli oggetti. Spostandosi sul mapping delle colonne otterremo la seguente maschera:

Tutti i parametri sono mappati.

Mentre i primi sono in ingresso l'ultimo (IDUtente) ritornerà il suo valore nella colonna derivata IDUtenteOUT prima definita. Nulla di più. Ora, eseguendo il SSIS, noteremo il nuovo ID con un dataviewer creato appena dopo l'oledbcommand:

Tutto questo è stato implementato all'interno di un dataflow. Il messagebox è creato dallo script component finale, per visualizzare il risultato già visibile dal data viewer. Quindi, è stato sufficiente aggiungere una colonna derivata e mappare il parametro su di essa per ottenere il valore del parametro di output della stored procedure.

Attenzione, non dimentichiamo la pulizia

-- pulizia

DROP TABLE Utenti

DROP PROCEDURE proc_Utenti

Stay tuned!

Categoria: SSIS 2005 tips
giovedì, 17 mag 2007 Ore. 02.00
Statistiche
  • Views Home Page: 479.126
  • Views Posts: 864.538
  • Views Gallerie: 498.859
  • n° Posts: 484
  • n° Commenti: 273



















Copyright © 2002-2007 - Blogs 2.0
dotNetHell.it | Home Page Blogs
ASP.NET 2.0 Windows 2003