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

Come utilizzare i parametri di output con SSIS

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 Task
La 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:
ADO
ADO.NET
OLEDB
ODBC
SQLMOBILE
EXCEL

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!
Categoria: SSIS 2005 Basics
lunedì, 01 set 2008 Ore. 18.34
Statistiche
  • Views Home Page: 600.671
  • Views Posts: 1.067.367
  • Views Gallerie: 641.166
  • n° Posts: 484
  • n° Commenti: 273



















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