Ultimamente ho avuto la necessità di dover ricavare il valore di un
parametro di output da una stored procedure. A dire il vero è spesso
importante ottenere un feedback da una stored procedure, sia per avere
un messaggio di ritorno, si per ricavare l'id dell'utlimo record con
identity inserito, ecc..
Già
qui, avevo illustrato come fare per utilizzarlo all'interno di un dataflow, tramite un
OLEDB Command TaskLa pratica è del tutto semplice, ma occorre fare chiarezza sul tipo di connessione utilizzato.
Quando si utilizza l'
Execute SQL Task il comportamento è un pochino differente.
Intanto è possibile selezionare il tipo di connessione da utilizzare fra le seguenti:
ADOADO.NETOLEDBODBCSQLMOBILEEXCEL
In base alla selezione, cambia il ConnectionManager da utilizzare
:
ADO ConnectionManager
ADONET ConnectionManager
OLEDB ConnectionManager
ODBC ConnectionManager
SQLMOBILE ConnectionManager
EXCEL ConnectionManager
In base al manager di connessione selezionato, il comando avrà sintassi differenti:
Ad esempio OLEDB
: exec StoredProc ? output ed ODBC:
{call StoredProc (?)}
L'impostazione
del task deve riferirsi quindi a queste specifiche. Qui di seguito
faremo un paio di esempi, i più frequenti, con OLEDB e ADO.NET
:
Preparazione
Questa
è una stored procedure alla quale passo un parametro smallint. Vi è un
parametro di output di tipo int che conterrà il valore del primo
parametro moltiplicato per 100:
USE tempdb;
GO
IF EXISTS (SELECT * FROM sysobjects WHERE
id = OBJECT_ID('dbo.proc_ParametroOut'))
DROP PROCEDURE dbo.proc_ParametroOut
GO
CREATE PROCEDURE dbo.proc_ParametroOut
@ParamIn smallint
, @ParamOut int = NULL OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SET @ParamOut =
@ParamIn * 100
END
GO
Subito dopo lo script, andiamo a creare il SSIS che eseguirà nei due modi la stored procedure:
1) Creare un connection manager di tipo ADO.NET (chiamato tempdbADONET) e uno di tipo OLEDB (chiamato tempdbOLEDB)
2) Creare un Execute SQL Task applicando il connection manager di tipo OLEDB
Come
possiamo notare abbiamo selezionato il connection manager di tipo OLEDB
ed abbiamo chiamato la stored procedure creata in precedenza, indicando
che il secondo è di tipo
output. BypassPrepare è impostata a True (la reference
qui).
Ecco come definire i parametri:
Da notare che nella colonna ParameterName (dopo la Data Type) è stata indicata la
posizione e non il nome del parametro definito nella stored procedure. Questo è
necessario per il corretto funzionamento dell'esecuzione.
3) Creare un Execute SQL Task applicando il connection manager di tipo ADONET
Da notare la proprietà
IsQueryStoredProcedure. Essa viene valorizzata a true se lo Statement corrisponde al nome di una stored procedure.
Il setting dei parametri è simile al precedente:
In
questo caso mettiamo il nome corretto dei parametri, poichè il nome
corrisponde al mapping verso i parametri della stored procedure.
Omettendo il nome corretto otterremo un errore come il seguente:
"Procedure or function 'proc_ParametroOut' expects parameter '@ParamIn', which was not supplied."4) Creare uno script che visualizzerà il parametro:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
#Region "Proprietà private"
Private ReadOnly Property ParamInput() As
Int16
Get
Return Convert.ToInt16(Dts.Variables("pID").Value)
End Get
End Property
Private ReadOnly Property ParamOutput() As
Int32
Get
Return Convert.ToInt16(Dts.Variables("pIDOut").Value)
End Get
End Property
#End Region
Public Sub Main()
'
MsgBox(String.Format("Param
In: {0}. Risultato (ParamIn * 100) = {1}.", Me.ParamInput, Me.ParamOutput))
'
Dts.TaskResult
= Dts.Results.Success
End Sub
End Class
Il SSIS apparirà come il seguente:
Per provare una o l'altra parte è sufficiente disabilitare uno degli execute task alla volta
.
Gli
esempi allegati sono solo in ADO.Net e OLEDB, che sono i più frequenti.
Per tutte le altre casistiche (ODBC, ADO, ecc) leggere la
documentazione nei link indicati all'inizio.
Stay tuned!
