Creazione della funzione per il recupero dei dati delle modifiche

Dopo avere completato il flusso di controllo per un pacchetto di Integration Services che esegue un caricamento incrementale dei dati delle modifiche, l'attività successiva consiste nella creazione di una funzione con valori di tabella per il recupero di tali dati. Questa funzione deve essere creata solo una volta, prima del primo caricamento incrementale.

[!NOTA]

La creazione di una funzione per il recupero dei dati delle modifiche rappresenta il secondo passaggio del processo di creazione di un pacchetto che esegue il caricamento incrementale di tali dati. Per una descrizione del processo completo di creazione del pacchetto, vedere Change Data Capture (SSIS).

Considerazioni sulla progettazione per le funzioni Change Data Capture

Per recuperare i dati delle modifiche, un componente di origine nel flusso di dati del pacchetto chiama una delle funzioni Change Data Capture seguenti:

• cdc.fn_cdc_get_net_changes_<capture_instance>   Per questa query, la singola riga restituita per ogni aggiornamento contiene lo stato finale di ogni riga modificata. Nella maggior parte dei casi i dati restituiti da una query sono necessari solo per modifiche totali. Per ulteriori informazioni, vedere cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL).

• cdc.fn_cdc_get_all_changes_<capture_instance>   Questa query restituisce tutte le modifiche apportate in ogni riga durante l'intervallo di acquisizione. Per ulteriori informazioni, vedere cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL).

Il componente di origine passa quindi i risultati restituiti dalla funzione a destinazioni e trasformazioni a valle, che applicano i dati delle modifiche alla destinazione finale.

Un componente di origine di Integration Services, tuttavia, non è in grado di chiamare direttamente tali funzioni di acquisizioni dei dati delle modifiche. Un componente di origine di Integration Services richiede i metadati sulle colonne restituite dalla query. Le funzioni Change Data Capture non definiscono le colonne della relativa tabella di output. Di conseguenza, le funzioni non restituiscono metadati sufficienti per un componente di origine di Integration Services.

Viene utilizzata invece una funzione wrapper con valori di tabella perché questo tipo di funzione definisce in modo esplicito le colonne della relativa tabella di output nella clausola RETURNS. Questa definizione esplicita di colonne fornisce i metadati necessari per un componente di origine di Integration Services. È necessario creare questa funzione per ogni tabella per cui si desidera recuperare dati delle modifiche.

Sono disponibili due opzioni per la creazione della funzione wrapper con valori di tabella che chiama la funzione di query Change Data Capture:

• È possibile chiamare la stored procedure di sistema sys.sp_cdc_generate_wrapper_function per creare automaticamente le funzioni con valori di tabella.

• È possibile scrivere una funzione con valori di tabella personalizzata tramite le linee guida e l'esempio presenti in questo argomento.

Chiamata di una stored procedure per la creazione della funzione con valori di tabella

Il modo più rapido e più semplice per creare le funzioni con valori di tabella necessarie consiste nel chiamare la stored procedure di sistema sys.sp_cdc_generate_wrapper_function. Questa stored procedure genera gli script per creare le funzioni wrapper progettate specificamente per soddisfare le esigenze di un componente di origine di Integration Services .

Importante
La stored procedure di sistema sys.sp_cdc_generate_wrapper_function non crea direttamente le funzioni wrapper ma genera gli script CREATE per le funzioni wrapper. Lo sviluppatore deve eseguire gli script CREATE generati dalla stored procedure affinché le funzioni wrapper possano essere chiamate da un pacchetto del caricamento incrementale.

Per comprendere il modo in cui utilizzare questa stored procedure di sistema è necessario capire le operazioni eseguite dalla procedura, gli script generati dalla procedura e le funzioni wrapper create dagli script.

Informazioni sulla stored procedure e relativo utilizzo

La stored procedure di sistema sys.sp_cdc_generate_wrapper_function genera gli script per creare le funzioni wrapper da utilizzare con i pacchetti di Integration Services.

Di seguito vengono riportate le prime righe della definizione della stored procedure:

CREATE PROCEDURE sys.sp_cdc_generate_wrapper_function

(

@capture\_instance sysname = null

@closed\_high\_end\_point bit = 1,

@column\_list = null,

@update\_flag\_list = null

)

Tutti i parametri per la stored procedure sono facoltativi. Se si chiama la stored procedure senza fornire alcun valore per i parametri, la stored procedure crea funzioni wrapper per tutte le istanze di acquisizione a cui si ha accesso.

[!NOTA]

Per ulteriori informazioni sulla sintassi di questa stored procedure e sui relativi parametri, vedere sys.sp_cdc_generate_wrapper_function (Transact-SQL).

La stored procedure genera sempre una funzione wrapper per restituire tutte le modifiche da ogni istanza di acquisizione. Se il parametro @supports\_net\_changes è stato impostato durante la creazione dell'istanza di acquisizione, la stored procedure genera anche una funzione wrapper per restituire le modifiche totali da ogni istanza di acquisizione applicabile.

La stored procedure restituisce un set di risultati con due colonne:

• Il nome della funzione wrapper generata dalla stored procedure. La stored procedure deduce il nome della funzione dal nome dell'istanza di acquisizione. Il nome della funzione è 'fn_all_changes_' seguito dal nome dell'istanza di acquisizione. Il prefisso utilizzato per la funzione di rilevamento delle modifiche delta, se creato, è 'fn_net_changes_'.

• L'istruzione CREATE per la funzione wrapper.

Informazioni sugli script creati dalla stored procedure e relativo utilizzo

Uno sviluppatore utilizza in genere un'istruzione INSERT...EXEC per chiamare la stored procedure sys.sp_cdc_generate_wrapper_function e salvare gli script creati dalla stored procedure in una tabella temporanea. Ogni script può quindi essere selezionato singolarmente ed eseguito per creare la funzione wrapper corrispondente. Uno sviluppatore può tuttavia utilizzare anche un set di comandi SQL per eseguire tutti gli script CREATE, come illustrato nel codice di esempio seguente:

create table #wrapper_functions
      (function_name sysname, create_stmt nvarchar(max))
insert into #wrapper_functions
exec sys.sp_cdc_generate_wrapper_function

declare @stmt nvarchar(max)
declare #hfunctions cursor local fast_forward for 
      select create_stmt from #wrapper_functions
open #hfunctions
fetch #hfunctions into @stmt
while (@@fetch_status <> -1)
begin
      exec sp_executesql @stmt
      fetch #hfunctions into @stmt
end
close #hfunctions
deallocate #hfunctions

Informazioni sulle funzioni create dalla stored procedure e relativo utilizzo

Per ripercorrere sistematicamente la cronologia dei dati modificati acquisiti, le funzioni wrapper generate richiedono che il parametro @end\_time di un intervallo sia il parametro @start\_time dell'intervallo successivo. Quando viene rispettata questa convenzione, le funzioni wrapper generate possono effettuare le attività seguenti:

• Eseguire il mapping dei valori di data e ora ai valori LSN utilizzati internamente.

• Verificare che i dati non vengano persi o ripetuti.

Per semplificare l'esecuzione di query su tutte le righe di una tabella delle modifiche, le funzioni wrapper generate supportano inoltre le convenzioni seguenti:

• Se il parametro @start\_time è null, le funzioni wrapper utilizzano il valore LSN minimo nell'istanza di acquisizione come limite inferiore della query.

• Se il parametro @end\_time è null, le funzioni wrapper utilizzano il valore LSN massimo nell'istanza di acquisizione come limite superiore della query.

La maggior parte degli utenti dovrebbe essere in grado di utilizzare le funzioni wrapper create dalla stored procedure di sistema sys.sp_cdc_generate_wrapper_function senza apportare modifiche. Tuttavia, per personalizzare le funzioni wrapper, è necessario personalizzare gli script CREATE prima di eseguire gli script.

Quando il pacchetto chiama le funzioni wrapper, il pacchetto deve fornire i valori per tre parametri. Questi tre parametri sono analoghi ai tre parametri utilizzati dalle funzioni Change Data Capture. I tre parametri sono:

• Il valore data/ora di inizio e il valore data/ora di fine per l'intervallo. Mentre le funzioni wrapper utilizzano i valori di data e ora come endpoint per l'intervallo di query, le funzioni Change Data Capture utilizzano due valori LSN come endpoint.

• Il filtro di riga. Il parametro @row\_filter\_option è uguale sia per le funzioni wrapper che per le funzioni Change Data Capture. Per ulteriori informazioni, vedere cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL) e cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL).

Il set di risultati restituito dalle funzioni wrapper include i dati seguenti:

• Tutte le colonne di dati di modifica richieste.

• Una colonna denominata __CDC_OPERATION che utilizza un campo di uno o due caratteri per identificare l'operazione associata alla riga. I valori validi per questo campo sono: 'I' per inserimento, 'D' per eliminazione, 'UO' per aggiornamento di valori vecchi e 'UN' per aggiornamento di valori nuovi.

• Flag di aggiornamento, quando necessari, visualizzati come colonne bit dopo il codice dell'operazione e nell'ordine specificato nel parametro @update\_flag\_list. Il nome di queste colonne viene creato aggiungendo '_uflag' al nome della colonna associato.

Se il pacchetto chiama una funzione wrapper che esegue una query su tutte le modifiche, la funzione wrapper restituisce anche le colonne __CDC_STARTLSN e __CDC_SEQVAL. Queste due colonne diventano rispettivamente la prima e la seconda colonna del set di risultati. La funzione wrapper ordina inoltre il set di risultati in base a queste due colonne.

Scrittura della funzione con valori di tabella personalizzata

È inoltre possibile utilizzare SQL Server Management Studio per scrivere una funzione wrapper con valori di tabella personalizzata che chiama la funzione di query Change Data Capture e archiviare la funzione wrapper con valori di tabella in SQL Server. Per ulteriori informazioni sulla creazione di una funzione Transact-SQL, vedere CREATE FUNCTION (Transact-SQL).

Nell'esempio seguente viene definita una funzione con valori di tabella per il recupero delle modifiche da una tabella Customer per l'intervallo di modifiche specificato. La funzione utilizza le funzioni Change Data Capture per eseguire il mapping tra i valori datetime e i valori dei numeri di sequenza del file di log (LSN) binario utilizzati dalle tabelle delle modifiche. Questa funzione gestisce inoltre diverse condizioni speciali:

• Quando viene passato un valore Null per l'ora di inizio, questa funzione utilizza il valore disponibile per primo.

• Quando viene passato un valore Null per l'ora di fine, questa funzione utilizza il valore disponibile per ultimo.

• Quando il numero LSN iniziale è uguale al numero LSN finale, a indicare in genere che non è presente alcun record per l'intervallo selezionato, la funzione viene chiusa.

Esempio di una funzione con valori di tabella che esegue query per i dati delle modifiche

CREATE function CDCSample.uf_Customer (
     @start_time datetime
    ,@end_time datetime
)
returns @Customer table (
     CustomerID int
    ,TerritoryID int
    ,CustomerType nchar(1)
    ,rowguid uniqueidentifier
    ,ModifiedDate datetime
    ,CDC_OPERATION varchar(1)
) as
begin
    declare @from_lsn binary(10), @to_lsn binary(10)

    if (@start_time is null)
        select @from_lsn = sys.fn_cdc_get_min_lsn('Customer')
    else
        select @from_lsn = sys.fn_cdc_increment_lsn(sys.fn_cdc_map_time_to_lsn('largest less than or equal',@start_time))

    if (@end_time is null)
        select @to_lsn = sys.fn_cdc_get_max_lsn()
    else
        select @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal',@end_time)

    if (@from_lsn = sys.fn_cdc_increment_lsn(@to_lsn))
        return

    -- Query for change data
    insert into @Customer
    select 
        CustomerID,    
        TerritoryID, 
        CustomerType, 
        rowguid, 
        ModifiedDate, 
        case __$operation
                when 1 then 'D'
                when 2 then 'I'
                when 4 then 'U'
                else null
         end as CDC_OPERATION
    from 
        cdc.fn_cdc_get_net_changes_Customer(@from_lsn, @to_lsn, 'all')

    return
end 
go

Recupero di metadati aggiuntivi con i dati di modifica

Anche se la funzione con valori di tabella creata dall'utente illustrata in precedenza utilizza solo la colonna __$operation, la funzione cdc.fn_cdc_get_net_changes_<capture_instance> restituisce quattro colonne di metadati per ciascuna riga di modifica. Se si desidera utilizzare questi valori nel flusso di dati, è possibile restituirli come colonne aggiuntive dalla funzione wrapper con valori di tabella.

Nome colonna	Tipo di dati	Descrizione
__$start_lsn	binary(10)	Valore LSN associato al commit della transazione per la modifica. Tutte le modifiche di cui è stato eseguito il commit nella stessa transazione condividono lo stesso valore LSN di commit. Se, ad esempio, un'operazione di aggiornamento nella tabella di origine modifica due diverse righe, la tabella delle modifiche conterrà quattro righe, due con i valori precedenti e due con i nuovi valori, ciascuna delle quali con lo stesso valore __$start_lsn.
__$seqval	binary(10)	Valore di sequenza utilizzato per ordinare le modifiche alle righe in una transazione.
__$operation	int	Operazione DML (Data Manipulation Language) associata alla modifica. I possibili valori sono i seguenti: 1 = eliminazione 2 = inserimento 3 = aggiornamento (valori precedenti all'operazione di aggiornamento) 4 = aggiornamento (valori successivi all'operazione di aggiornamento)
__$update_mask	varbinary(128)	Maschera di bit basata su numeri ordinali di colonna della tabella delle modifiche che identifica le colonne modificate. È possibile esaminare questo valore se è necessario determinare le colonne modificate.
<colonne della tabella di origine acquisite>	variabile	Le colonne rimanenti restituite dalla funzione sono le colonne della tabella di origine identificate come colonne acquisite durante la creazione dell'istanza di acquisizione. Se in origine non è stata specificata alcuna colonna nell'elenco delle colonne acquisite, verranno restituite tutte le colonne della tabella di origine.

Per ulteriori informazioni, vedere cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL).

Passaggio successivo

Dopo avere creato la funzione con valori di tabella per l'esecuzione di query per i dati delle modifiche, il passaggio successivo consiste nell'iniziare a progettare il flusso di dati nel pacchetto.

Argomento successivo: Recupero e comprensione dei dati delle modifiche

Rimanere aggiornati con Integration Services Per i download, gli articoli, gli esempi e i video Microsoft più recenti, oltre alle soluzioni selezionate dalla community, visitare la pagina Integration Services sul sito MSDN: Visitare la pagina relativa a Integration Services su MSDN Per ricevere notifica automatica su questi aggiornamenti, sottoscrivere i feed RSS disponibili nella pagina.