Change Data Capture (SSIS)Change Data Capture (SSIS)

Change Data Capture in SQL ServerSQL Serveroffre una soluzione efficace alla sfida posta dall'esecuzione di caricamenti incrementali da tabelle di origine in data mart e data warehouse.In SQL ServerSQL Server, change data capture offers an effective solution to the challenge of efficiently performing incremental loads from source tables to data marts and data warehouses.

Informazioni su Change Data CaptureWhat is Change Data Capture?

Le tabelle di origine vengono modificate nel tempo.Source tables change over time. Un data mart o un data warehouse basato su tali tabelle deve riflettere le modifiche.A data mart or data warehouse that is based on those tables needs to reflect these changes. Un processo di copia periodica di uno snapshot dell'intera origine, tuttavia, richiede troppo tempo e l'utilizzo di una quantità eccessiva di risorse.However, a process that periodically copies a snapshot of the entire source consumes too much time and resources. Approcci alternativi che includono colonne di tipo timestamp, trigger o query complesse riducono spesso le prestazioni e aumentano la complessità.Alternate approaches that include timestamp columns, triggers, or complex queries often hurt performance and increase complexity. È necessario un flusso affidabile di dati delle modifiche strutturato in modo che possa essere applicato con facilità dagli utenti alle rappresentazioni di destinazione dei dati.What is needed is a reliable stream of change data that is structured so that it can easily be applied by consumers to target representations of the data. Change Data Capture in SQL ServerSQL Server è in grado di offrire tale soluzione.Change data capture in SQL ServerSQL Server provides this solution.

La funzionalità Change Data Capture di Motore di databaseDatabase Engine consente di acquisire attività di inserimento, aggiornamento ed eliminazione applicate a tabelle SQL ServerSQL Server e rende disponibili i dettagli relativi alle modifiche in un formato relazionale semplice da utilizzare.The change data capture feature of the Motore di databaseDatabase Engine captures insert, update, and delete activity applied to SQL ServerSQL Server tables, and makes the details of the changes available in an easily-consumed, relational format. Le tabelle delle modifiche utilizzate da Change Data Capture contengono colonne che riflettono la struttura di colonne delle tabelle di origine rilevate, insieme ai metadati necessari per comprendere le modifiche apportate riga per riga.The change tables used by change data capture contain columns that mirror the column structure of the tracked source tables, along with the metadata needed to understand the changes that have occurred on a row by row basis.

Nota

Change Data Capture non è disponibile in tutte le edizioni di MicrosoftMicrosoft SQL ServerSQL Server.Change data capture is not available in every edition of MicrosoftMicrosoft SQL ServerSQL Server. Per un elenco delle funzionalità supportate dalle edizioni di SQL ServerSQL Server, vedere Funzionalità supportate dalle edizioni di SQL Server 2016.For a list of features that are supported by the editions of SQL ServerSQL Server, see Features Supported by the Editions of SQL Server 2016.

Funzionamento di Change Data Capture in Integration ServicesHow Change Data Capture Works in Integration Services

Tramite un pacchetto di Integration ServicesIntegration Services è possibile raccogliere facilmente i dati delle modifiche nei database di SQL ServerSQL Server per eseguire caricamenti incrementali efficaci in un data warehouse.An Integration ServicesIntegration Services package can easily harvest the change data in the SQL ServerSQL Server databases to perform efficient incremental loads to a data warehouse. Prima che sia possibile utilizzare Integration ServicesIntegration Services per caricare i dati delle modifiche, un amministratore deve tuttavia abilitare Change Data Capture nel database e nelle tabelle da cui si desidera acquisire le modifiche.However, before you can use Integration ServicesIntegration Services to load change data, an administrator must enable change data capture on the database and the tables from which you want to capture changes. Per altre informazioni su come configurare Change Data Capture in un database, vedere Abilitare e disabilitare Change Data Capture (SQL Server).For more information on how to configure change data capture on a database, see Enable and Disable Change Data Capture (SQL Server).

Dopo che un amministratore ha abilitato Change Data Capture nel database, è possibile creare un pacchetto per l'esecuzione del caricamento incrementale di tali dati.Once an administrator has enabled change data capture on the database, you can create a package that performs an incremental load of the change data. Nel diagramma seguente vengono illustrati i passaggi per la creazione di tale pacchetto che esegue un caricamento incrementale da una singola tabella:The following diagram shows the steps for creating such a package that performs an incremental load from a single table:

Modificare i passaggi di creazione del pacchetto di dati acquisizioneChange Data Capture Package Creation Steps

Come illustrato nel diagramma precedente, la creazione di un pacchetto per l'esecuzione di un caricamento incrementale dei dati modificati comporta i passaggi seguenti:As shown in the previous diagram, creating a package that performs an incremental load of changed data involves the following steps:

Passaggio 1: Progettazione del flusso di controlloStep 1: Designing the Control Flow
Nel flusso di controllo del pacchetto è necessario definire le attività seguenti:In the control flow in the package, the following tasks need to be defined:

  • Calcolare i valori datetime di inizio e di fine per l'intervallo di modifiche apportate ai dati di origine da recuperare.Calculate the starting and ending datetime values for the interval of changes to the source data that you want to retrieve.

    Per calcolare tali valori, usare un'attività Esegui SQL o espressioni Integration ServicesIntegration Services con funzioni datetime .To calculate these values, use an Execute SQL task or Integration ServicesIntegration Services expressions with datetime functions. È quindi necessario archiviare gli endpoint in variabili del pacchetto da utilizzare in seguito nel pacchetto.You then store these endpoints in package variables for use later in the package.

    Per altre informazioni: Definizione di un intervallo dei dati delle modificheFor more information: Specify an Interval of Change Data

  • Determinare se i dati delle modifiche per l'intervallo selezionato sono pronti.Determine whether the change data for the selected interval is ready. Questo passaggio è necessario in quanto il processo di acquisizione asincrono potrebbe non avere ancora raggiunto l'endpoint selezionato.This step is necessary because the asynchronous capture process might not yet have reached the selected endpoint.

    Per determinare se i dati sono pronti, iniziare con un contenitore Ciclo For per rimandare l'esecuzione, se necessario, fino a quando i dati delle modifiche per l'intervallo selezionato non saranno pronti.To determine whether the data is ready, start with a For Loop container to delay execution, if necessary, until the change data for the selected interval is ready. Nel contenitore Ciclo For utilizzare un'attività Esegui SQL per eseguire una query sulle tabelle di mapping temporale gestite da Change Data Capture.Inside the loop container, use an Execute SQL task to query the time mapping tables maintained by change data capture. Usare quindi un'attività Script che chiama il metodo Thread.Sleep o un'altra attività Esegui SQL con un'istruzione WAITFOR per rimandare temporaneamente l'esecuzione del pacchetto, se necessario.Then, use a Script task that calls the Thread.Sleep method, or another Execute SQL task with a WAITFOR statement, to delay the execution of the package temporarily, if necessary. Facoltativamente, utilizzare un'altra attività Script per registrare una condizione di errore o un timeout.Optionally, use another Script task to log an error condition or a timeout.

    Per altre informazioni: Come determinare se i dati delle modifiche sono prontiFor more information: Determine Whether the Change Data Is Ready

  • Preparare la stringa di query che verrà utilizzata per eseguire una query per i dati delle modifiche.Prepare the query string that will be used to query for the change data.

    Utilizzare un'attività Script o un'attività Esegui SQL per assemblare l'istruzione SQL da utilizzare per eseguire una query per le modifiche.Use a Script task or an Execute SQL task to assemble the SQL statement that will be used to query for changes.

    Per altre informazioni: Preparazione dell'esecuzione di una query per i dati delle modificheFor more information: Prepare to Query for the Change Data

    Passaggio 2: Configurazione della query per i dati delle modificheStep 2: Setting Up the Query for Change Data
    Creare la funzione con valori di tabella che eseguirà una query per i dati.Create the table-valued function that will query for the data.

    Usare SQL Server Management StudioSQL Server Management Studio per sviluppare e salvare la query.Use SQL Server Management StudioSQL Server Management Studio to develop and save the query.

    Per altre informazioni: Recupero e comprensione dei dati delle modificheFor more information: Retrieve and Understand the Change Data

    Passaggio 3: Progettazione del flusso di datiStep 3: Designing the Data Flow
    Nel flusso di dati del pacchetto è necessario definire le attività seguenti:In the data flow of the package, the following tasks need to be defined:

  • Recuperare i dati delle modifiche dalle tabelle delle modifiche.Retrieve the change data from the change tables.

    Per recuperare i dati, utilizzare un componente di origine per eseguire una query sulle tabelle delle modifiche comprese nell'intervallo selezionato.To retrieve the data, use a source component to query the change tables for the changes that fall within the selected interval. L'origine chiama una funzione Transact-SQL con valori di tabella che deve essere stata creata in precedenza.The source calls a Transact-SQL table-valued function that you must have previously created.

    Per altre informazioni: Recupero e comprensione dei dati delle modificheFor more information: Retrieve and Understand the Change Data

  • Suddividere le modifiche in inserimenti, aggiornamenti ed eliminazioni per l'elaborazione.Split the changes into inserts, updates, and deletes for processing.

    Per suddividere le modifiche, utilizzare una trasformazione Suddivisione condizionale per indirizzare inserimenti, aggiornamenti ed eliminazioni a output diversi per l'elaborazione appropriata.To split the changes, use a Conditional Split transformation to direct inserts, updates, and deletes to different outputs for appropriate processing.

    Per altre informazioni: Elaborazione di inserimenti, aggiornamenti ed eliminazioniFor more information: Process Inserts, Updates, and Deletes

  • Applicare gli inserimenti, le eliminazioni e gli aggiornamenti alla destinazione.Apply the inserts, deletes, and updates to the destination.

    A tale scopo, utilizzare un componente di destinazione per applicare gli inserimenti alla destinazione.To apply the changes to the destination, use a destination component to apply the inserts to the destination. Utilizzare inoltre trasformazioni Comando OLE DB con istruzioni UPDATE e DELETE con parametri per applicare aggiornamenti ed eliminazioni alla destinazione.Also, use OLE DB Command transformations with parameterized UPDATE and DELETE statements to apply updates and deletes to the destination. È inoltre possibile applicare aggiornamenti ed eliminazioni utilizzando componenti di destinazione per salvare le righe in tabelle temporanee.You can also apply updates and deletes by using destination components to save the rows to temporary tables. Utilizzare quindi le attività Esegui SQL per eseguire operazioni di aggiornamento bulk e di eliminazione bulk sulla destinazione dalle tabelle temporanee.Then, use Execute SQL tasks to perform bulk update and bulk delete operations against the destination from the temporary tables.

    Per altre informazioni: Applicazione delle modifiche alla destinazioneFor more information: Apply the Changes to the Destination

Modificare i dati di più tabelleChange Data from Multiple Tables

Il processo illustrato nel diagramma e nei passaggi precedenti prevede un caricamento incrementale da una singola tabella.The process outlined in the previous diagram and steps involves an incremental load from a single table. Il processo per eseguire un caricamento incrementale da più tabelle è identico.When having to perform an incremental load from multiple tables, the overall process is the same. È tuttavia necessario modificare la progettazione del pacchetto per adattarlo all'elaborazione di più tabelle.However, the design of the package needs to be changed to accommodate the processing of multiple tables. Per altre informazioni su come creare un pacchetto che esegue un caricamento incrementale da più tabelle, vedere Esecuzione di un caricamento incrementale di più tabelle.For more information on how to create a package that performs an incremental load from multiples tables, see Perform an Incremental Load of Multiple Tables.

Esempi di pacchetti Change Data CaptureSamples of Change Data Capture Packages

Integration ServicesIntegration Services offre due esempi che illustrano come usare la funzionalità Change Data Capture nei pacchetti. provides two samples that demonstrate how to use change data capture in packages. Per altre informazioni, vedere gli argomenti seguenti:For more information, see the following topics:

Intervento nel blog sul carico incrementale con schema progettuale di SSISsu sqlblog.comBlog entry, SSIS Design Pattern – Incremental Load, on sqlblog.com