Preparare l'esecuzione di una query per i dati delle modifiche

Articolo
05/23/2023

Si applica a:SQL Server SSIS Integration Runtime in Azure Data Factory

Nel flusso di controllo di un pacchetto di Integration 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.

Nota

La seconda attività per il flusso di controllo consiste nel verificare che i dati delle modifiche per l'intervallo selezionato siano pronti. Per altre informazioni su questa attività, vedere Determinare se i dati delle modifiche sono pronti. Per una descrizione del processo complessivo di progettazione del flusso di controllo, vedere Change Data Capture (SSIS).

Considerazioni sulla progettazione

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. Un componente di origine nel flusso di dati chiama questa funzione. Per informazioni su questo componente di origine, vedere Recuperare e comprendere i dati delle modifiche.

I componenti di origine di Integration 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. La maggior parte delle origini, pertanto, non può chiamare una funzione con parametri direttamente.

Per passare i parametri di input alla funzione, sono disponibili due opzioni di progettazione:

Assemblare la query con parametri come stringa. È 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. È quindi possibile archiviare tale stringa in una variabile del pacchetto e utilizzarla per impostare la proprietà SqlCommand di un componente di origine. Questo approccio ha esito positivo perché il componente di origine non richiede più informazioni sui parametri.

Nota

Uno script precompilato è soggetto a un overhead minore rispetto a un'attività Esegui SQL.
Usare un wrapper con parametri. In alternativa, è possibile creare una stored procedure con parametri come wrapper che chiama la funzione valutata a livello di tabella con parametri. Questo approccio ha esito positivo perché un componente di origine è in grado di derivare correttamente le informazioni sui parametri per una stored procedure.

In questo argomento viene utilizzata la prima opzione di progettazione, assemblando una query con parametri come stringa.

Preparazione della 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.

Per configurare le variabili del pacchetto

Nella finestra Variabili di SQL Server Data Tools (SSDT) creare una variabile con un tipo di dati stringa per memorizzare la stringa di query restituita dall'attività Esegui SQL.

In questo esempio viene utilizzato il nome di variabile 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. Nelle due procedure seguenti viene descritto come configurare tali componenti.

Per utilizzare un'attività Script per concatenare la stringa di query

Nella scheda Flusso di controllo aggiungere un'attività Script al pacchetto dopo il contenitore Ciclo For e connettere tale contenitore all'attività.

Nota

In questa procedura si presuppone che il pacchetto esegua un caricamento incrementale da una singola tabella. 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. Per altre informazioni, vedere Eseguire un caricamento incrementale di più tabelle.
Nella pagina Scriptin Editor attività Script selezionare le opzioni seguenti:
1. Per ReadOnlyVariablesselezionare User::DataReady, User::ExtractStartTimee User::ExtractEndTime .
2. Per ReadWriteVariablesselezionare User::SqlDataQuery nell'elenco.
Nella pagina Scriptin Editor attività Script fare clic su Modifica script per aprire l'ambiente di sviluppo dello script.

Nella routine Main immettere uno dei segmenti di codice seguenti:

Se si utilizza il linguaggio di programmazione C#, immettere le righe di codice seguenti:

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 -

Se si usa il linguaggio di programmazione Visual Basic, immettere le righe di codice seguenti:

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

Lasciare la riga di codice predefinita, che restituisce DtsExecResult.Success dall'esecuzione dello script.
Chiudere l'ambiente di sviluppo dello script ed Editor attività Script.

Per utilizzare un'attività Esegui SQL per concatenare la stringa di query

Nella scheda Flusso di controllo aggiungere un'attività Esegui SQL al pacchetto dopo il contenitore Ciclo For e connettere tale contenitore all'attività.

Nota

In questa procedura si presuppone che il pacchetto esegua un caricamento incrementale da una singola tabella. 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. Per altre informazioni, vedere Eseguire un caricamento incrementale di più tabelle.
Nella pagina Generalein Editor attività Esegui SQL selezionare le opzioni seguenti:
1. Per ResultSet, selezionare Riga singola.
2. Configurare una connessione valida al database di origine.
3. Per SQLSourceType, selezionare Input diretto.
4. Per SQLStatementimmettere l'istruzione SQL seguente:
```
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. 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.
Nella pagina Mapping parametri di Editor attività Esegui SQLcreare i mapping seguenti:
1. Eseguire il mapping tra la variabile DataReady e il parametro 0.
2. Eseguire il mapping tra la variabile ExtractStartTime e il parametro 1.
3. Eseguire il mapping tra la variabile ExtractEndTime e il parametro 2.
Nella pagina Set di risultati di Editor attività Esegui SQLeseguire il mapping tra il nome del risultato e la variabile SqlDataQuery.

Il nome del risultato è il nome della singola colonna restituita, ovvero 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. Il codice seguente rappresenta un esempio di tale stringa di query:

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

Aggiunta di un'attività Flusso di dati

L'ultimo passaggio nella progettazione del flusso di controllo per il pacchetto consiste nell'aggiungere un'attività Flusso di dati.

Per aggiungere un'attività Flusso di dati e completare il flusso di controllo

Nella scheda Flusso di controllo aggiungere un'attività Flusso di dati e connettere l'attività che ha concatenato la stringa di query.

Passaggio successivo

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.

Argomento successivo:Creare la funzione per il recupero dei dati delle modifiche