Creazione della funzione per il recupero dei dati delle modificheCreate the Function to Retrieve the Change Data

Dopo avere completato il flusso di controllo per un pacchetto di Integration ServicesIntegration 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.After completing the control flow for an Integration ServicesIntegration Services package that performs an incremental load of change data, the next task is to create a table-valued function that retrieves the change data. Questa funzione deve essere creata solo una volta, prima del primo caricamento incrementale.You only have to create this function one time before the first incremental load.

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.The creation of a function to retrieve the change data is the second step in the process of creating a package that performs an incremental load of change data. Per una descrizione del processo completo di creazione del pacchetto, vedere Change Data Capture (SSIS) (Modificare i dati di acquisizione (SSIS&#41).For a description of the overall process for creating this package, see Change Data Capture (SSIS).

Considerazioni sulla progettazione per le funzioni Change Data CaptureDesign Considerations for Change Data Capture Functions

Per recuperare i dati delle modifiche, un componente di origine nel flusso di dati del pacchetto chiama una delle funzioni Change Data Capture seguenti:To retrieve change data, a source component in the data flow of the package calls one of the following change data capture query functions:

  • 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.cdc.fn_cdc_get_net_changes_<capture_instance> For this query, the single row returned for each update contains the final state of each changed row. Nella maggior parte dei casi i dati restituiti da una query sono necessari solo per modifiche totali.In most cases, you only need the data returned by a query for net changes. Per altre informazioni, vedere cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL).For more information, see 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.cdc.fn_cdc_get_all_changes_<capture_instance> This query returns all the changes that have occurred in each row during the capture interval. Per altre informazioni, vedere cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL).For more information, see 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.The source component then takes the results returned by the function and passes them to downstream transformations and destinations, which apply the change data to the final destination.

    Un componente di origine di Integration ServicesIntegration Services , tuttavia, non è in grado di chiamare direttamente le funzioni di acquisizioni dei dati delle modifiche.However, an Integration ServicesIntegration Services source component cannot call these change data capture functions directly. Un componente di origine di Integration ServicesIntegration Services richiede i metadati sulle colonne restituite dalla query.An Integration ServicesIntegration Services source component requires metadata about the columns that the query returns. Le funzioni Change Data Capture non definiscono le colonne della relativa tabella di output.The change data capture functions do not define the columns of their output table. Di conseguenza, le funzioni non restituiscono metadati sufficienti per un componente di origine di Integration ServicesIntegration Services .Thus, these functions do not return sufficient metadata for an Integration ServicesIntegration Services source component.

    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.Instead, you use a table-valued wrapper function because this kind of function explicitly defines the columns of its output table in its RETURNS clause. Questa definizione esplicita di colonne fornisce i metadati necessari per un componente di origine di Integration ServicesIntegration Services .This explicit definition of columns provides the metadata that an Integration ServicesIntegration Services source component needs. È necessario creare questa funzione per ogni tabella per cui si desidera recuperare dati delle modifiche.You have to create this function for each table for which you want to retrieve change data.

    Sono disponibili due opzioni per la creazione della funzione wrapper con valori di tabella che chiama la funzione di query Change Data Capture:You have two options for creating the table-valued wrapper function that calls the change data capture query function:

  • È possibile chiamare la stored procedure di sistema sys.sp_cdc_generate_wrapper_function per creare automaticamente le funzioni con valori di tabella.You can call the sys.sp_cdc_generate_wrapper_function system stored procedure to create the table-valued functions for you.

  • È possibile scrivere una funzione con valori di tabella personalizzata tramite le linee guida e l'esempio presenti in questo argomento.You can write your own table-valued function by using the guidelines and the example in this topic.

Chiamata di una stored procedure per la creazione della funzione con valori di tabellaCalling a Stored Procedure to Create the Table-valued Function

Il modo più semplice e rapido per creare le funzioni con valori di tabella necessarie consiste nel chiamare la stored procedure di sistema sys.sp_cdc_generate_wrapper_function .The quickest and easiest way to create the table-valued functions that you need is to call the sys.sp_cdc_generate_wrapper_function system stored procedure. Questa stored procedure genera gli script per creare le funzioni wrapper specificamente progettate per soddisfare le esigenze di un componente di origine di Integration ServicesIntegration Services .This stored procedure generates scripts to create wrapper functions that are designed specifically to meet the needs of an Integration ServicesIntegration Services source component.

Importante

La stored procedure di sistema sys.sp_cdc_generate_wrapper_function non crea direttamente le funzioni wrapper,The sys.sp_cdc_generate_wrapper_function system stored procedure does not directly create the wrapper functions. ma genera gli script CREATE per le funzioni wrapper.Instead, the stored procedure generates the CREATE scripts for the wrapper functions. Lo sviluppatore deve eseguire gli script CREATE generati dalla stored procedure affinché le funzioni wrapper possano essere chiamate da un pacchetto del caricamento incrementale.The developer must run the CREATE scripts that the stored procedure generates before an incremental load package can call the wrapper functions.

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.To understand how to use this system stored procedure, you should understand what the procedure does, what scripts the procedure generates, and what wrapper functions the scripts create.

Informazioni sulla stored procedure e relativo utilizzoUnderstanding and Using the Stored Procedure

La stored procedure di sistema sys.sp_cdc_generate_wrapper_function genera gli script per creare le funzioni wrapper da usare con i pacchetti di Integration ServicesIntegration Services .The sys.sp_cdc_generate_wrapper_function system stored procedure generates scripts to create wrapper functions for use by Integration ServicesIntegration Services packages.

Di seguito vengono riportate le prime righe della definizione della stored procedure:Here are the first few lines of the definition of the 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.All the parameters for the stored procedure are optional. 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.If you call the stored procedure without supplying values for any of the parameters, the stored procedure creates wrapper functions for all the capture instances to which you have access.

Nota

Per altre informazioni sulla sintassi di questa stored procedure e sui relativi parametri, vedere sys.sp_cdc_generate_wrapper_function (Transact-SQL).For more information about the syntax of this stored procedure and its parameters, see 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.The stored procedure always generates a wrapper function to return all changes from each capture instance. Se durante la creazione dell'istanza di acquisizione è stato impostato il parametro @supports_net_changes , la stored procedure genera anche una funzione wrapper per restituire le modifiche totali da ogni istanza di acquisizione applicabile.If the @supports_net_changes parameter was set when the capture instance was created, the stored procedure also generates a wrapper function to return net changes from each applicable capture instance.

La stored procedure restituisce un set di risultati con due colonne:The stored procedure returns a result set with two columns:

  • Il nome della funzione wrapper generata dalla stored procedure.The name of the wrapper function that the stored procedure has generated. La stored procedure deduce il nome della funzione dal nome dell'istanza di acquisizione.This stored procedure derives the function name from the name of the capture instance name. Il nome della funzione è 'fn_all_changes_' seguito dal nome dell'istanza di acquisizione.(The function name is 'fn_all_changes_' followed by the capture instance name. Il prefisso utilizzato per la funzione di rilevamento delle modifiche delta, se creato, è 'fn_net_changes_'.The prefix used for the net changes function, if it is created, is 'fn_net_changes_'.)

  • L'istruzione CREATE per la funzione wrapper.The CREATE statement for the wrapper function.

Informazioni sugli script creati dalla stored procedure e relativo utilizzoUnderstanding and Using the Scripts Created by the Stored Procedure

Uno sviluppatore usa 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.Typically, a developer would use an INSERT...EXEC statement to call the sys.sp_cdc_generate_wrapper_function stored procedure and save the scripts that the stored procedure creates to a temporary table. Ogni script può quindi essere selezionato singolarmente ed eseguito per creare la funzione wrapper corrispondente.Each script could then be individually selected and run to create the corresponding wrapper function. Uno sviluppatore può tuttavia utilizzare anche un set di comandi SQL per eseguire tutti gli script CREATE, come illustrato nel codice di esempio seguente:However, a developer could also use one set of SQL commands to run all the CREATE scripts, as shown in the following sample code:

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 utilizzoUnderstanding and Using the Functions Created by the Stored Procedure

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.To systematically walk the timeline of captured change data, the generated wrapper functions expect that the @end_time parameter for one interval will be the @start_time parameter for the subsequent interval. Quando viene rispettata questa convenzione, le funzioni wrapper generate possono effettuare le attività seguenti:When this convention is followed, the generated wrapper functions can do the following tasks:

  • Eseguire il mapping dei valori di data e ora ai valori LSN utilizzati internamente.Map the date/time values to the LSN values that are used internally.

  • Verificare che i dati non vengano persi o ripetuti.Ensure that no data is lost or repeated.

    Per semplificare l'esecuzione di query su tutte le righe di una tabella delle modifiche, le funzioni wrapper generate supportano inoltre le convenzioni seguenti:To make querying for all rows of a change table simpler, the generated wrapper functions also support the following conventions:

  • Se il @start_time parametro è null, le funzioni wrapper utilizzano il valore LSN minimo nell'istanza di acquisizione come limite inferiore della query.If the @start_time parameter is null, the wrapper functions use the lowest LSN value in the capture instance as the lower bound of the query.

  • Se il @end_time parametro è null, le funzioni wrapper utilizzano il valore LSN massimo nell'istanza di acquisizione come limite superiore della query.If the @end_time parameter is null, the wrapper functions use the highest LSN value in the capture instance as the upper bound of the query.

    La maggior parte degli utenti dovrebbe essere in grado di usare le funzioni wrapper create dalla stored procedure di sistema sys.sp_cdc_generate_wrapper_function senza apportare modifiche.Most users should be able to use the wrapper functions that the sys.sp_cdc_generate_wrapper_function system stored procedure creates without modification. Tuttavia, per personalizzare le funzioni wrapper, è necessario personalizzare gli script CREATE prima di eseguire gli script.However, to customize the wrapper functions, you have to customize the CREATE scripts before you run the scripts.

    Quando il pacchetto chiama le funzioni wrapper, il pacchetto deve fornire i valori per tre parametri.When your package calls the wrapper functions, the package must supply values for three parameters. Questi tre parametri sono analoghi ai tre parametri utilizzati dalle funzioni Change Data Capture.These three parameters are like the three parameters that the change data capture functions use. I tre parametri sono:These three parameters are as follows:

  • Il valore data/ora di inizio e il valore data/ora di fine per l'intervallo.The starting date/time value and the ending date/time value for the interval. 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.While the wrapper functions use date/time values as the end points for the query interval, the change data capture functions use two LSN values as the end points.

  • Il filtro di riga.The row filter. Il parametro @row_filter_option è uguale sia per le funzioni wrapper che per le funzioni di Change Data Capture.For both the wrapper functions and the change data capture functions, the @row_filter_option parameter is the same. Per altre informazioni, vedere cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL) e cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL).For more information, see cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL) and cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL).

    Il set di risultati restituito dalle funzioni wrapper include i dati seguenti:The result set returned by the wrapper functions includesthe following data:

  • Tutte le colonne di dati di modifica richieste.All of the requested columns of change data.

  • Una colonna denominata __CDC_OPERATION che utilizza un campo di uno o due caratteri per identificare l'operazione associata alla riga.A column named __CDC_OPERATION that uses a one- or two-character field to identify the operation that is associated with the row. 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.The valid values for this field are as follows: ‘I’ for insert, ‘D’ for delete, ‘UO’ for update old values, and ‘UN’ for update new values.

  • Flag di aggiornamento, quando necessari, visualizzati come colonne bit dopo il codice dell'operazione e nell'ordine specificato nel parametro @update_flag_list .Update flags, when you request them, that appear as bit columns after the operation code and in the order that is specified in the @update_flag_list parameter. Il nome di queste colonne viene creato aggiungendo '_uflag' al nome della colonna associato.These columns are named by appending ‘_uflag’ to the associated column name.

    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.If your package calls a wrapper function that queries for all changes, the wrapper function also returns the columns, __CDC_STARTLSN and __CDC_SEQVAL. Queste due colonne diventano rispettivamente la prima e la seconda colonna del set di risultati.These two columns become the first and second columns, respectively, of the result set. La funzione wrapper ordina inoltre il set di risultati in base a queste due colonne.The wrapper function also sorts the result set based on these two columns.

Scrittura della funzione con valori di tabella personalizzataWriting Your Own Table-Value Function

È anche possibile usare SQL Server Management StudioSQL 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 ServerSQL Server.You can also use SQL Server Management StudioSQL Server Management Studio to write your own table-valued wrapper function that calls the change data capture query function, and store the table-valued wrapper function in SQL ServerSQL Server. Per altre informazioni sulla creazione di una funzione Transact-SQL, vedere CREATE FUNCTION (Transact-SQL).For more information about how to create a Transact-SQL function, see 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.The following example defines a table-valued function that retrieves changes from a Customer table for the specified change interval. La funzione usa le funzioni di Change Data Capture per eseguire il mapping tra i valori datetime e i valori dei numeri di sequenza del file di log (LSN) binario usati dalle tabelle delle modifiche.This function uses change data capture functions to map the datetime values to the binary log sequence number (LSN) values that the change tables use internally. Questa funzione gestisce inoltre diverse condizioni speciali:This function also handles several special conditions:

  • Quando viene passato un valore Null per l'ora di inizio, questa funzione utilizza il valore disponibile per primo.When a null value is passed for the starting time, this function uses the earliest available value.

  • Quando viene passato un valore Null per l'ora di fine, questa funzione utilizza il valore disponibile per ultimo.When a null value is passed for the ending time, this function uses the latest available value.

  • 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.When the starting LSN is equal to the ending LSN, which usually indicates that there are no records for the selected interval, this function exits.

Esempio di una funzione con valori di tabella che esegue query per i dati delle modificheExample of a Table-Value Function that Queries for Change Data

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 modificaRetrieving Additional Metadata with the Change Data

Anche se la funzione con valori di tabella creata dall'utente illustrata in precedenza usa solo la colonna __$operation, la funzione cdc.fn_cdc_get_net_changes_<capture_instance> restituisce quattro colonne di metadati per ogni riga di modifica.Although the user-created table-valued function shown earlier uses only the __$operation column, the cdc.fn_cdc_get_net_changes_<capture_instance> function returns four columns of metadata for each change row. Se si desidera utilizzare questi valori nel flusso di dati, è possibile restituirli come colonne aggiuntive dalla funzione wrapper con valori di tabella.If you want to use these values in your data flow, you can return them as additional columns from the table-valued wrapper function.

Nome colonnaColumn name Tipo di datiData type DescriptionDescription
__$start_lsn__$start_lsn binary(10)binary(10) Valore LSN associato al commit della transazione per la modifica.LSN associated with the commit transaction for the change.

Tutte le modifiche di cui è stato eseguito il commit nella stessa transazione condividono lo stesso valore LSN di commit.All changes committed in the same transaction share the same commit LSN. 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, ognuna delle quali con lo stesso valore __$start_lsn .For example, if an update operation on the source table modifies two different rows, the change table will contain four rows (two with the old values and two with the new values), each with the same __$start_lsn value.
__$seqval__$seqval binary(10)binary(10) Valore di sequenza utilizzato per ordinare le modifiche alle righe in una transazione.Sequence value that is used to order the row changes in a transaction.
__$operation__$operation intint Operazione DML (Data Manipulation Language) associata alla modifica.The data manipulation language (DML) operation associated with the change. I possibili valori sono i seguenti:Can be one of the following:

1 = eliminazione1 = delete

2 = inserimento2 = insert

3 = aggiornamento (valori precedenti all'operazione di aggiornamento)3 = update (Values before the update operation.)

4 = aggiornamento (valori successivi all'operazione di aggiornamento)4 = update (Values after the update operation.)
__$update_mask__$update_mask varbinary(128)varbinary(128) Maschera di bit basata su numeri ordinali di colonna della tabella delle modifiche che identifica le colonne modificate.A bitmask that is based on the column ordinals of the change table identifying those columns that changed. È possibile esaminare questo valore se è necessario determinare le colonne modificate.You could examine this value if you had to determine which columns have changed.
<colonne di tabella di origine acquisite ><captured source table columns> variabilevaries Le colonne rimanenti restituite dalla funzione sono le colonne della tabella di origine identificate come colonne acquisite durante la creazione dell'istanza di acquisizione.The remaining columns returned by the function are the columns from the source table that were identified as captured columns when the capture instance was created. Se in origine non è stata specificata alcuna colonna nell'elenco delle colonne acquisite, verranno restituite tutte le colonne della tabella di origine.If no columns were originally specified in the captured column list, all columns in the source table are returned.

Per altre informazioni, vedere cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL).For more information, see cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL).

Passaggio successivoNext Step

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.After you have created the table-valued function that queries for change data, the next step is to start designing the data flow in the package.

Argomento successivo: Recuperare e comprendere i dati delle modificheNext topic: Retrieve and Understand the Change Data