Preparazione dell'esecuzione di una query per i dati delle modifichePrepare to Query for the Change Data

Nel flusso di controllo di un pacchetto di Integration ServicesIntegration Services per l'esecuzione di un caricamento incrementale dei dati delle modifiche, la terza e ultima attività consiste nel preparare l'esecuzione di una query per i dati delle modifiche e nell'aggiungere un'attività Flusso di dati.In the control flow of an Integration ServicesIntegration Services package that performs an incremental load of change data, the third and final task is to prepare to query for the change data and add a Data Flow task.

Nota

La seconda attività per il flusso di controllo consiste nel verificare che i dati delle modifiche per l'intervallo selezionato siano pronti.The second task for the control flow is to ensure that the change data for the selected interval is ready. Per altre informazioni su questa attività, vedere Determinare se i dati delle modifiche sono pronti.For more information about this task, see Determine Whether the Change Data Is Ready. Per una descrizione del processo complessivo di progettazione del flusso di controllo, vedere Change Data Capture (SSIS).For a description of the overall process of designing the control flow, see Change Data Capture (SSIS).

Considerazioni sulla progettazioneDesign Considerations

Per recuperare i dati delle modifiche, è necessario chiamare una funzione Transact-SQL con valori di tabella che accetti gli endpoint dell'intervallo come parametri di input e restituisca i dati delle modifiche per l'intervallo specificato.To retrieve the change data, you will call a Transact-SQL table-valued function that accepts the endpoints of the interval as input parameters and returns change data for the specified interval. Un componente di origine nel flusso di dati chiama questa funzione.A source component in the data flow calls this function. Per informazioni su questo componente di origine, vedere Recuperare e comprendere i dati delle modifiche.For information about this source component, see Retrieve and Understand the Change Data.

I componenti di origine Integration ServicesIntegration Services usati più di frequente, incluse l'origine OLE DB, l'origine ADO e l'origine ADO NET, non possono derivare informazioni sui parametri per una funzione valutata a livello di tabella.The most frequently used Integration ServicesIntegration Services source components, including the OLE DB source, the ADO source, and the ADO NET source, cannot derive parameter information for a table-valued function. La maggior parte delle origini, pertanto, non può chiamare una funzione con parametri direttamente.Therefore, most sources cannot call a parameterized function directly.

Per passare i parametri di input alla funzione, sono disponibili due opzioni di progettazione:You have two design options for passing the input parameters to the function:

  • Assemblare la query con parametri come stringa.Assemble the parameterized query as a string. È possibile utilizzare un'attività Script o un'attività Esegui SQL per assemblare una stringa SQL dinamica con valori di parametro specificati a livello di codice nella stringa.You can use a Script task or an Execute SQL task to assemble a dynamic SQL string with parameter values hard-coded into the string. È quindi possibile archiviare tale stringa in una variabile del pacchetto e utilizzarla per impostare la proprietà SqlCommand di un componente di origine.Then, you can store this string in a package variable and use it to set the SqlCommand property of a source component. Questo approccio ha esito positivo perché il componente di origine non richiede più informazioni sui parametri.This approach succeeds because the source component no longer requires parameter information.

    Nota

    Uno script precompilato è soggetto a un overhead minore rispetto a un'attività Esegui SQL.A precompiled script incurs less overhead than an Execute SQL task.

  • Usare un wrapper con parametri.Use a parameterized wrapper. In alternativa, è possibile creare una stored procedure con parametri come wrapper che chiama la funzione valutata a livello di tabella con parametri.Alternatively, you can create a parameterized stored procedure as a wrapper that calls the parameterized table-valued function. Questo approccio ha esito positivo perché un componente di origine è in grado di derivare correttamente le informazioni sui parametri per una stored procedure.This approach succeeds because a source component can successfully derive parameter information for a stored procedure.

    In questo argomento viene utilizzata la prima opzione di progettazione, assemblando una query con parametri come stringa.This topic uses the first design option and assembles a parameterized query as a string.

Preparazione della queryPreparing the Query

Prima che sia possibile concatenare i valori dei parametri di input in una singola stringa di query, è necessario configurare le variabili del pacchetto necessarie per la query.Before you can concatenate the values of the input parameters into a single query string, you have to set up the package variables that the query needs.

Per configurare le variabili del pacchettoTo set up package variables

  • Nella finestra SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)Variabili in creare una variabile con un tipo di dati stringa per memorizzare la stringa di query restituita dall'attività Esegui SQL.In SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT), in the Variables window, create a variable with a string data type to hold the query string returned by the Execute SQL Task.

    In questo esempio viene utilizzato il nome di variabile SqlDataQuery.This example uses the variable name, SqlDataQuery.

    Dopo avere creato la variabile del pacchetto, è possibile utilizzare un'attività Script o un'attività Esegui SQL per concatenare i valori dei parametri di input.With the package variable created, you can use either a Script task or Execute SQL task to concatenate the values of the input parameters. Nelle due procedure seguenti viene descritto come configurare tali componenti.The following two procedures describe how to configure these components.

Per utilizzare un'attività Script per concatenare la stringa di queryTo use a Script task to concatenate the query string

  1. Nella scheda Flusso di controllo aggiungere un'attività Script al pacchetto dopo il contenitore Ciclo For e connettere tale contenitore all'attività.On the Control Flow tab, add a Script task to the package after the For Loop container and connect the For Loop container to the task.

    Nota

    In questa procedura si presuppone che il pacchetto esegua un caricamento incrementale da una singola tabella.This procedure assumes that the package performs an incremental load from a single table. Se il pacchetto esegue il caricamento da più tabelle e ha un pacchetto padre con più pacchetti figlio, questa attività viene aggiunta come primo componente a ciascun pacchetto figlio.If the package loads from multiple tables and has a parent package with multiple child packages, this task would be added as the first component to each child package. Per altre informazioni, vedere Eseguire un caricamento incrementale di più tabelle.For more information, see Perform an Incremental Load of Multiple Tables.

  2. Nella pagina Scriptin Editor attività Script selezionare le opzioni seguenti:In the Script Task Editor, on the Script page, select the following options:

    1. Per ReadOnlyVariablesselezionare User::DataReady, User::ExtractStartTimee User::ExtractEndTime .For ReadOnlyVariables, select User::DataReady, User::ExtractStartTime, and User::ExtractEndTime from the.

    2. Per ReadWriteVariablesselezionare User::SqlDataQuery nell'elenco.For ReadWriteVariables, select User::SqlDataQuery from the list.

  3. Nella pagina Scriptin Editor attività Script fare clic su Modifica script per aprire l'ambiente di sviluppo dello script.In the Script Task Editor, on the Script page, click Edit Script to open the script development environment.

  4. Nella routine Main immettere uno dei segmenti di codice seguenti:In the Main procedure, enter one of the following code segments:

    • Se si utilizza il linguaggio di programmazione C#, immettere le righe di codice seguenti:If you are programming in C#, enter the following lines of code:

      int dataReady;  
      System.DateTime extractStartTime;  
      System.DateTime extractEndTime;  
      string sqlDataQuery;  
      
      dataReady = (int)Dts.Variables["DataReady"].Value;  
      extractStartTime = (System.DateTime)Dts.Variables["ExtractStartTime"].Value;  
      extractEndTime = (System.DateTime)Dts.Variables["ExtractEndTime"].Value;  
      
      if (dataReady == 2)  
        {  
        sqlDataQuery = "SELECT * FROM CDCSample.uf_Customer('" + string.Format("{0:yyyy-MM-dd hh:mm:ss}", extractStartTime) + "', '" + string.Format("{0:yyyy-MM-dd hh:mm:ss}", extractEndTime) + "')";  
        }  
      else  
        {  
        sqlDataQuery = "SELECT * FROM CDCSample.uf_Customer(null" + ", '" + string.Format("{0:yyyy-MM-dd hh:mm:ss}", extractEndTime) + "')";  
        }  
      
      Dts.Variables["SqlDataQuery"].Value = sqlDataQuery;  
      

      - - oppure -- or -

    • Se si usa il linguaggio di programmazione Visual BasicVisual Basic, immettere le righe di codice seguenti:If you are programming in Visual BasicVisual Basic, enter the following lines of code:

      Dim dataReady As Integer  
      Dim extractStartTime As Date  
      Dim extractEndTime As Date  
      Dim sqlDataQuery As String  
      
      dataReady = CType(Dts.Variables("DataReady").Value, Integer)  
      extractStartTime = CType(Dts.Variables("ExtractStartTime").Value, Date)  
      extractEndTime = CType(Dts.Variables("ExtractEndTime").Value, Date)  
      
      If dataReady = 2 Then  
        sqlDataQuery = "SELECT * FROM CDCSample.uf_Customer('" & _  
            String.Format("{0:yyyy-MM-dd hh:mm:ss}", extractStartTime) & _  
            "', '" & _  
            String.Format("{0:yyyy-MM-dd hh:mm:ss}", extractEndTime) & _  
            "')"  
      Else  
        sqlDataQuery = "SELECT * FROM CDCSample.uf_Customer(null" & _  
            ", '" & _  
            String.Format("{0:yyyy-MM-dd hh:mm:ss}", extractEndTime) & _  
            "')"  
      End If  
      
      Dts.Variables("SqlDataQuery").Value = sqlDataQuery  
      
  5. Lasciare la riga di codice predefinita, che restituisce DtsExecResult.Success dall'esecuzione dello script.Leave the default line of code which returns DtsExecResult.Success from the execution of the script.

  6. Chiudere l'ambiente di sviluppo dello script ed Editor attività Script.Close the script development environment and the Script Task Editor.

Per utilizzare un'attività Esegui SQL per concatenare la stringa di queryTo use an Execute SQL task to concatenate the query string

  1. Nella scheda Flusso di controllo aggiungere un'attività Esegui SQL al pacchetto dopo il contenitore Ciclo For e connettere tale contenitore all'attività.On the Control Flow tab, add an Execute SQL task to the package after the For Loop container and connect the For Loop container to this task.

    Nota

    In questa procedura si presuppone che il pacchetto esegua un caricamento incrementale da una singola tabella.This procedure assumes that the package performs an incremental load from a single table. Se il pacchetto esegue il caricamento da più tabelle e ha un pacchetto padre con più pacchetti figlio, questa attività viene aggiunta come primo componente a ciascun pacchetto figlio.If the package loads from multiple tables and has a parent package with multiple child packages, this task would be added as the first component to each child package. Per altre informazioni, vedere Eseguire un caricamento incrementale di più tabelle.For more information, see Perform an Incremental Load of Multiple Tables.

  2. Nella pagina Generalein Editor attività Esegui SQL selezionare le opzioni seguenti:In the Execute SQL Task Editor, on the General page, select the following options:

    1. Per ResultSetselezionare Riga singola.For ResultSet, select Single row.

    2. Configurare una connessione valida al database di origine.Configure a valid connection to the source database.

    3. Per SQLSourceType, selezionare Input diretto.For SQLSourceType, select Direct input.

    4. Per SQLStatementimmettere l'istruzione SQL seguente:For SQLStatement, enter the following SQL statement:

      declare @ExtractStartTime datetime,  
      @ExtractEndTime datetime,   
      @DataReady int  
      
      select @DataReady = ?,   
      @ExtractStartTime = ?,   
      @ExtractEndTime = ?  
      
      if @DataReady = 2  
      select N'select * from CDCSample.uf_Customer'  
      + N'('''+ convert(nvarchar(30),@ExtractStartTime,120)  
      + ''', '''  
      + convert(nvarchar(30),@ExtractEndTime,120) + ''') '   
      as SqlDataQuery  
      else  
      select N'select * from CDCSample.uf_Customer'  
      + N'(null, '''  
      + convert(nvarchar(30),@ExtractEndTime,120)  
      + ''') '  
      as SqlDataQuery  
      

      Nota

      In questo esempio la clausola else genera una query per il caricamento iniziale dei dati delle modifiche passando un valore Null per la data e l'ora di inizio.The else clause in this sample generates a query for the initial load of change data by passing a null value for the starting date and time. Questo esempio non si applica a uno scenario in cui le modifiche apportate prima dell'attivazione di Change Data Capture devono essere caricate anche nel data warehouse.This sample does not address the scenario in which changes that were made before change data capture was enabled also have to be uploaded to the data warehouse.

  3. Nella pagina Mapping parametri di Editor attività Esegui SQLcreare i mapping seguenti:On the Parameter Mapping page of the Execute SQL Task Editor, do the following mapping:

    1. Eseguire il mapping tra la variabile DataReady e il parametro 0.Map the DataReady variable to parameter 0.

    2. Eseguire il mapping tra la variabile ExtractStartTime e il parametro 1.Map the ExtractStartTime variable to parameter 1.

    3. Eseguire il mapping tra la variabile ExtractEndTime e il parametro 2.Map the ExtractEndTime variable to parameter 2.

  4. Nella pagina Set di risultati di Editor attività Esegui SQLeseguire il mapping tra il nome del risultato e la variabile SqlDataQuery.On the Result Set page of the Execute SQL Task Editor, map the Result Name to the SqlDataQuery variable.

    Il nome del risultato è il nome della singola colonna restituita, ovvero SqlDataQuery.The Result Name is the name of the single column that is returned, SqlDataQuery.

    Nelle procedure precedenti è stata configurata un'attività per la preparazione di una stringa di query con valori stringa specificati a livello di codice per i parametri di input.The previous procedures configure a task that prepares a query string with hard-coded string values for the input parameters. Il codice seguente rappresenta un esempio di tale stringa di query:The following code is an example of such a query string:

    select * from CDCSample. uf_Customer('2007-06-11 14:21:58', '2007-06-12 14:21:58')

Aggiunta di un'attività Flusso di datiAdding a Data Flow Task

L'ultimo passaggio nella progettazione del flusso di controllo per il pacchetto consiste nell'aggiungere un'attività Flusso di dati.The last step in designing the control flow for the package is to add a Data Flow task.

Per aggiungere un'attività Flusso di dati e completare il flusso di controlloTo add a Data Flow task and complete the control flow

  • Nella scheda Flusso di controllo aggiungere un'attività Flusso di dati e connettere l'attività che ha concatenato la stringa di query.On the Control Flow tab, add a Data Flow task and connect the task that concatenated the query string.

Passaggio successivoNext Step

Dopo avere preparato la stringa di query e avere configurato l'attività Flusso di dati, il passaggio successivo consiste nel creare la funzione valutata a livello di tabella per il recupero dei dati delle modifiche dal database.After you prepare the query string and configure the Data Flow task, the next step is create the table-valued function that will retrieve the change data from the database.

Argomento successivo: Creare la funzione per il recupero dei dati delle modificheNext topic: Create the Function to Retrieve the Change Data