All'interno di un SSIS, e più in particolare di un Data Flow Task, è possibile ricavare il resultset sorgente tramite l'utilizzo di stored procedure. OLEDB Source DataAdapter consente proprio la creazione di set di dati con comandi T-SQL, chiamate a stored procedure comprese..
Ma attenzione, non sempre è così scontato ottenere i metadati.
Questo è un problema che si verifica molto spesso.. e purtroppo esistono soltanto dei workaround. Il problema sta sui metadati salvati nel catalog.
In alcuni particolari casi di stored procedure (ne vedremo un esempio di seguito) i metadati sono proprio irraggiungibili da SSIS e quello che si ottiene è un OLEDB Source completamente inutilizzabile.
Supponiamo ad esempio di avere una stored procedure come questa, che crea e consuma una tabella temporanea:

CREATE PROCEDURE proc_Temporanea
AS
BEGIN
  SET NOCOUNT ON;

  CREATE TABLE #tempTable
    (
      id int Identity(1,1) PRIMARY KEY CLUSTERED,
      descrizione varchar(30)
    )
  INSERT #tempTable
  SELECT 'uno'

  INSERT #tempTable
  SELECT 'due'

  INSERT #tempTable
  SELECT 'tre'
 
  SELECT id, descrizione FROM #tempTable
END

Questa semplice stored procedure, utilizzata come comando sorgente di un OLEDB Source, non torna alcun metadato, anche se premendo il tasto Preview i dati ci sono effettivamente..


Spostandosi infatti sulla sezione columns, questa sarà vuota, rendendo del tutto inutilizzabile la di fatto la sorgente dati.


Questo perchè mentre tramite il tasto preview lanciamo effettivamente il comando, nella sezione columns andiamo ad interrogare le tabelle di sistema, come ad esempio la sys.columns.
Le stored procedure non hanno metadati come le tabelle o le funzioni o le viste.. ragion per cui possiamo ottenere lo stesso risultato con l'ausilio di user function.
Eccone un esempio:

CREATE FUNCTION ufn_ProvaTable ()
RETURNS @tempTable TABLE
(
  id int Identity(1,1) PRIMARY KEY CLUSTERED,
  descrizione varchar(30)
)
AS
BEGIN
  INSERT @tempTable
  SELECT 'uno'
  INSERT @tempTable
  SELECT 'due'
  INSERT @tempTable
  SELECT 'tre'

  RETURN
END

La funzione descritta possiede nel catalog di sistema tutte le informazioni necessarie per gestire i metadati con SSIS. Utilizzandola al posto della stored procedure avremo infatti la sezione columns popolata:




Bisogna quindi fare attenzione ad utilizzare le stored procedure come semplici resultset in SSIS. Visto che in molti casi possiamo ottenere il medesimo comportamento utilizzando anche altri oggetti forniti da SQL Server.
Il workaround delle funzioni, che fino ad oggi non avevo descritto, in quanto non si riusciva a trovare molta documentazione in merito, è confermato anche da Jamie Thomson nel suo post.
In esso viene aggiunto un approfondimento sul catalog di sistema ed un richiamo ad un interessante post..
stay tuned!