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! 