Query parametriche in Integration Services

In SSIS è possibile definire query parametriche, ovvero query SQL create dinamicamente in fase di esecuzione.

In questi esempi si leggono dal data base Northwind i dati relativi a un prodotto. L'id del prodotto si trova in una variabile. Saranno spiegati tre modi differenti per costruire query sql dinamiche, che potranno essere usati a seconda delle necessità nei progetti BI di Visual Studio.

Questo articolo presuppone una conoscenza almeno basilare di Sql, Visual Studio e SSIS.

Query statica

Partiamo da una semplice query SQL statica, con il parametro ProductID cablato nella query stessa:

SELECT [ProductID]
      ,[ProductName]
      ,[SupplierID]
      ,[CategoryID]
      ,[QuantityPerUnit]
      ,[UnitPrice]
      ,[UnitsInStock]
      ,[UnitsOnOrder]
      ,[ReorderLevel]
      ,[Discontinued]
FROM [Northwind].[dbo].[Products] 
WHERE ProductID = 24

Si crea quindi un Connection Manager di tipo OleDB, poi un Ole DB Source. Si editano le proprietà di quest'ultimo specificando la connessione, il tipo di query (in questo caso SQL Command)  e il testo SQL:

A questo punto, andando su Colums si noterà che Visual Studio ha già elaborato l'elenco delle colonne risultanti. Tale elenco potrà poi essere usato ad esempio da un Flat File Destination per mapparle e salvarle su un file.  

Query con parametri

C'è la possibilità di salvare il valore dell'id in una variabile:

Visual Studio >> SSIS >> Variables >> Aggiungere una variabile chiamata ad esempio id_prodotto di tpo Int32 e con valore di default = 4

Tornare sul Ole DB Souce creato in precedenza e sostituire il 24 con un punto di domanda. La parte finale della query SQL diventa:

WHERE ProductID = ?

Nella stessa maschera cliccare poi sul pulsante Parameters a destra.
Aggiungere un parametro scegliendo nella tendina la variabile creata in precedenza. Il nome del parametro è indifferente.

Da adesso in fase di run-time il punto esclamativo sarà sostituito dal valore contenuto nella variabile.

In caso si vogliano usare più parametri, tenere presente che devono essere definiti nello stesso ordine in cui appaiono nella query, rappresentati sempre da punti di domanda.
E' bene sapere che con altri tipi di Connection Manager cambia anche la nomenclatura dei parametri.

Espressione

I due esempi precendenti, così come quello successivo sono applicabili sia ad un Ole DB Data Source che ad un SQL Task. Quest'ultimo però permette anche di definire delle espressioni che vengono calcolate in fase di esecuzione del SSIS.

In questo caso conviene specificare nel campo SQL Source Type il comando SQL senza la clausola WHERE. Questo permette a Visual Studio di elaborare la query in fase di design, quindi le colonne risultanti (l'elenco di campi specificati dopo SELECT) vengono letti e sono mappabili dal Task successivo. In realtà a run-time il testo SQL verrà interamente sostituito da quello specificato nell'Expression.

Dalle proprietà del SQL Task  andare in Expressions e definire una nuova espressione per la proprietà SqlStatementSource.

"SELECT [ProductID]
      ,[ProductName]
      ,[SupplierID]
      ,[CategoryID]
      ,[QuantityPerUnit]
      ,[UnitPrice]
      ,[UnitsInStock]
      ,[UnitsOnOrder]
      ,[ReorderLevel]
      ,[Discontinued]
FROM [Northwind].[dbo].[Products] 
WHERE ProductID = " + (DT_STR,6,1252)@[User::id_prodotto]

Come testo dell'espressione si specifica una stringa tra doppi apici (") alla quale si concatena la variabile id_prodotto creata in precedenza convertita in stringa.
Cliccando su Evaluate Expression è possibile vedere la stringa Sql risultante.

Stringa Sql in VB.Net

Un altro metodo, a mio avviso il più flessibile, consiste nell'usare uno Script Task per costruire dinamicamente il testo della query SQL.

Si definisce quindi una variabile di tipo stringa da SISS. Anche in questo caso conviene già definire il testo senza la parte WHERE. Attenzione che deve stare tutta su una riga, quindi tramite un editor come Notepad++ bisogna sostituire tutti i ritorni a capo (\r\n) con uno spazio vuoto.

Creare poi uno Script Task, andare su Edit >> Script e scrivere il nome delle variabili da rendere accessibili via script.

Poi cliccare su Design Sript per aprire l'editor Visual Basic e scrivere il seguente codice:

Public Sub Main()
    Dim strSQL As String
    strSQL = "SELECT * FROM Products WHERE ProductID = " _
        & CType(Dts.Variables("id_prodotto").Value, String)
    Dts.Variables("strSQL").Value = strSQL
    Dts.TaskResult = Dts.Results.Success
End Sub

Si usa la notazione Dts.Variables("id_prodotto").Value per accedere alle variabili definite in SISS. Notare che bisogna sempre effettuare una conversione esplicita in questo caso in tipo String.

 

 

Autore: Sergio Roberto Boarina