Configurare SQL Server in un'attività Copy

Questo articolo illustra come utilizzare l'attività Copy nella pipeline di dati per copiare dati da e verso un database SQL Server.

Configurazione supportata

Per la configurazione di ogni scheda nell'attività Copy, leggere le rispettive sezioni seguenti.

• Generali
• Origine
• Destinazione
• Mapping
• Impostazioni

Generali

Per configurare la scheda di impostazioni Generali, vedere la guida alle impostazioni Generali.

Origine

Le proprietà seguenti sono supportate per SQL Server nella scheda Origine di un'attività Copy.

Sono richieste le seguenti proprietà:

• Tipo di archivio dati: selezionare Esterno.

• Connessione: selezionare una connessione a un SQL Server dall'elenco delle connessioni. Se la connessione non esiste, creare una nuova connessione a SQL Server selezionando Nuovo.

• Tipo di connessione: selezionare SQL Server.

• Usa query: specificare come vengono letti i dati. È possibile scegliere Tabella, Query o Stored procedure. L'elenco seguente descrive la configurazione di ogni impostazione:

  • Tabella: legge i dati dalla tabella specificata. Selezionare la tabella di origine dall'elenco a discesa o selezionare Modifica per immetterla manualmente.

  • Query: specificare la query SQL personalizzata per leggere i dati. Un esempio è select * from MyTable. In alternativa, selezionare l'icona a forma di matita da modificare nell'editor di codice.

    

  • Stored procedure: utilizzare la stored procedure che legge i dati dalla tabella di origine. L'ultima istruzione SQL deve essere un'istruzione SELECT nella stored procedure.

    • Nome stored procedure: selezionare la stored procedure o specificare il nome della stored procedure manualmente quando si seleziona Modifica per leggere i dati dalla tabella di origine.

    • Parametri della stored procedure: specificare i valori per i parametri della stored procedure. I valori consentiti sono coppie nome-valore. I nomi e le maiuscole/minuscole dei parametri devono corrispondere ai nomi e alle maiuscole/minuscole dei parametri della stored procedure. È possibile selezionare Importa parametri per ottenere i parametri della stored procedure.

      

In Avanzate, è possibile specificare i campi seguenti:

• Timeout query (minuti): specificare il timeout per l'esecuzione del comando di query, il valore predefinito è 120 minuti. Se per questa proprietà è impostato un parametro, i valori consentiti sono intervalli di tempo, ad esempio "02:00:00" (120 minuti).

• Livello di isolamento: specifica il comportamento di blocco della transazione per l'origine SQL. I valori consentiti sono: Nessuno, Lettura confermata, Lettura non confermata, Lettura ripetibile, Serializzabile, Snapshot. Se non è specificato, viene utilizzato il livello di isolamento predefinito del database. Per altri dettagli, vedere IsolationLevel Enum.

  

• Opzione partizione: specifica le opzioni di partizionamento dei dati usate per caricare dati da SQL Server. I valori consentiti sono: Nessuno (impostazione predefinita), Partizioni fisiche della tabella e Intervallo dinamico. Quando è abilitata un'opzione di partizione (diversa da Nessuno), il grado di parallelismo per il caricamento simultaneo di dati da SQL Server è controllato dal valore Grado di parallelismo di copia presente nella scheda delle impostazioni dell'attività Copy.

  • Nessuno: scegliere questa impostazione per non utilizzare alcuna partizione.

  • Partizioni fisiche della tabella: quando si usa una partizione fisica, la colonna di partizione e il meccanismo vengono determinati automaticamente in base alla definizione della tabella fisica.

  • Intervallo dinamico: quando si usa la query con la funzionalità parallela abilitata, è necessario il parametro di partizione dell'intervallo (?DfDynamicRangePartitionCondition). Query di esempio: SELECT * FROM <TableName> WHERE ?DfDynamicRangePartitionCondition.

    • Nome colonna di partizionamento: specificare il nome della colonna di origine nel tipo integer o date/datetime (int, smallint, bigint, date, smalldatetime, datetime, datetime2 o datetimeoffset) che verrà usata nel partizionamento per intervalli per la copia parallela. Se non specificato, la chiave primaria della tabella viene rilevata automaticamente e usata come colonna di partizione.

      Se si usa una query per recuperare i dati di origine, associare ?DfDynamicRangePartitionCondition nella clausola WHERE. Per un esempio, vedere la sezione Copia parallela dal database SQL.

    • Limite superiore partizione: specificare il valore massimo della colonna di partizione per la suddivisione dell'intervallo di partizioni. Questo valore viene usato per decidere lo stride di partizione, non per filtrare le righe nella tabella. Tutte le righe nella tabella o nel risultato della query verranno partizionate e copiate. Se non specificato, l'attività Copy rileva automaticamente il valore. Per un esempio, vedere la sezione Copia parallela dal database SQL.

    • Limite inferiore partizione: specificare il valore minimo della colonna di partizione per la suddivisione dell'intervallo di partizioni. Questo valore viene usato per decidere lo stride di partizione, non per filtrare le righe nella tabella. Tutte le righe nella tabella o nel risultato della query verranno partizionate e copiate. Se non specificato, l'attività Copy rileva automaticamente il valore. Per un esempio, vedere la sezione Copia parallela dal database SQL.

• Colonne aggiuntive: aggiungere altre colonne di dati per archiviare il percorso relativo o il valore statico dei file di origine. L'espressione è supportata per la seconda opzione.

Notare i punti seguenti:

• Se Query è specificato per l'origine, l’attività Copy esegue questa query nell’origine di SQL Server per ottenere i dati. In alternativa, è possibile specificare una stored procedure indicando i Nome stored procedure e Parametri stored procedure se la stored procedure accetta parametri.
• Quando si usa la stored procedure nell'origine per recuperare dati, tenere presente che se la stored procedure è progettata per restituire schemi diversi quando viene passato un valore di parametro diverso, è possibile che si verifichi un errore o un risultato imprevisto durante l'importazione dello schema dall'interfaccia utente o quando si copiano dati nel database SQL con la creazione automatica della tabella.

Destinazione

Le proprietà seguenti sono supportate per SQL Server nella scheda Destinazione di un'attività Copy.

Sono richieste le seguenti proprietà:

• Tipo di archivio dati: selezionare Esterno.

• Connessione: selezionare una connessione SQL Server dall'elenco delle connessioni. Se la connessione non esiste, creare una nuova connessione a SQL Server selezionando Nuovo.

• Tipo di connessione: selezionare SQL Server.

• Opzione Tabella: è possibile scegliere Usa esistente per usare la tabella specificata. In alternativa, scegliere Creazione automatica tabella per creare automaticamente una tabella di destinazione se la tabella non esiste nello schema di origine; questa selezione non è supportata quando la stored procedure viene usata come comportamento di scrittura.

  Se si seleziona Usa esistente:

  • Tabella: selezionare la tabella di destinazione dall'elenco a discesa. In alternativa, selezionare Modifica per immettere manualmente il nome della tabella.

  Se si seleziona Crea tabella automaticamente:

  • Tabella: specificare il nome per la tabella di destinazione creata automaticamente.

In Avanzate, è possibile specificare i campi seguenti:

• Comportamento scrittura: definisce il comportamento di scrittura quando l'origine è costituita da file di un archivio dati basato su file. È possibile scegliere Inserisci, Upsert o Stored procedure.

  • Inserisci: scegliere questa opzione per usare il comportamento di inserimento scrittura per caricare i dati in SQL Server.

  • Upsert: scegliere questa opzione per usare il comportamento di scrittura upsert per caricare i dati in SQL Server.

    • Usa TempDB: specificare se utilizzare la tabella temporanea globale o quella fisica come tabella provvisoria per l’operazione upsert. Per impostazione predefinita, il servizio utilizza la tabella temporanea globale come tabella provvisoria e questa proprietà è selezionata.

      

    • Selezionare lo schema del database utente: quando l'opzione Usa TempDB non è selezionata, specificare lo schema provvisorio per la creazione di una tabella provvisoria se viene usata una tabella fisica.

      Nota

      L'utente deve disporre dell'autorizzazione per la creazione e l'eliminazione della tabella. Per impostazione predefinita, una tabella provvisoria condividerà lo stesso schema di una tabella di destinazione.

      

    • Colonne chiave: specificare i nomi colonna per l'identificazione univoca delle righe. È possibile usare una singola chiave o una serie di chiavi. Se non specificato, viene usata la chiave primaria.

  • Stored procedure: utilizzare il nome della stored procedure che definisce come applicare i dati di origine in una tabella di destinazione. Questa stored procedure viene richiamata per batch. Per operazioni che vengono eseguite una sola volta e che non riguardano dati di origine, ad esempio un'eliminazione o un troncamento, usare la proprietà Script di pre-copia.

    • Nome stored procedure: selezionare la stored procedure o specificare manualmente il nome della stored procedure quando si seleziona Modifica per leggere i dati dalla tabella di origine.

    • Parametri di stored procedure:

      • Tipo tabella: specificare il nome di un tipo di tabella da usare nella stored procedure. L'attività Copy fa in modo che i dati spostati siano disponibili in una tabella temporanea con questo tipo di tabella. Il codice della stored procedure può quindi unire i dati di cui è in corso la copia con i dati esistenti.
      • Nome del parametro di tipo di tabella: specificare il nome del parametro del tipo di tabella specificato nella stored procedure.
      • Parametri: specificare i valori dei parametri della stored procedure. I valori consentiti sono coppie nome-valore. I nomi e le maiuscole/minuscole dei parametri devono corrispondere ai nomi e alle maiuscole/minuscole dei parametri della stored procedure. È possibile selezionare Importa parametri per ottenere i parametri della stored procedure.

      

• Blocco tabella inserimento in blocco: scegliere Sì o No (impostazione predefinita). Utilizzare questa impostazione per migliorare le prestazioni di copia durante un'operazione di inserimento in blocco in una tabella senza indici da più client. È possibile specificare questa proprietà quando si seleziona Inserisci o Upsert come comportamento di scrittura. Per altre informazioni, andare a BULK INSERT (Transact-SQL)

• Script di pre-copia: specificare uno script per l'attività Copy da eseguire prima di scrivere i dati in una tabella di destinazione in ogni esecuzione. È possibile usare questa proprietà per pulire i dati precaricati.

• Timeout del batch di scrittura: specificare il tempo di attesa per il completamento dell'operazione di inserimento batch prima del timeout. Il valore consentito è timespan. Se non si specifica alcun valore, viene utilizzato il timeout predefinito, "02:00:00".

• Dimensioni batch di scrittura: specificare il numero di righe da inserire nella tabella SQL per batch. Il valore consentito è integer (numero di righe). Per impostazione predefinita, il servizio determina in modo dinamico le dimensioni appropriate del batch in base alle dimensioni della riga.

• Numero massimo di connessioni simultanee: limite massimo di connessioni simultanee stabilite all'archivio dati durante l'esecuzione dell'attività. Specificare un valore solo quando si desidera limitare le connessioni simultanee.

Mapping

Per la configurazione della scheda Mapping, se non si applica SQL Server con la tabella di creazione automatica come destinazione, passare a Mapping.

Se si applica SQL Server con la tabella di creazione automatica come destinazione, ad eccezione della configurazione in Mapping, è possibile modificare il tipo per le colonne di destinazione. Dopo aver selezionato Importa schemi, è possibile specificare il tipo di colonna nella destinazione.

Ad esempio, il tipo per la colonna ID nell'origine è int ed è possibile modificarlo in tipo float quando si esegue il mapping alla colonna di destinazione.

Impostazione

Per la configurazione della scheda Impostazioni, vedere Configurare le altre impostazioni nella scheda Impostazioni.

Copia parallela dal database SQL

Il connettore SQL Server nell'attività Copy fornisce il partizionamento dei dati predefinito per copiare i dati in parallelo. È possibile trovare le opzioni di partizionamento dei dati nella tabella Origine dell'attività Copy.

Quando si abilita la copia partizionata, l’attività Copy esegue query parallele sull'origine di SQL Server per caricare i dati in base alle partizioni. Il grado parallelo è controllato dal Grado di parallelismo di copia nella scheda delle impostazioni dell'attività Copy. Ad esempio, se si imposta Grado di parallelismo di copia su quattro, il servizio genera ed esegue simultaneamente quattro query in base all'opzione e alle impostazioni di partizione specificate e ogni query recupera una parte di dati da SQL Server.

È preferibile abilitare la copia parallela con il partizionamento dei dati specialmente quando si caricano notevoli quantità di dati dal database di SQL Server. Di seguito sono riportate le configurazioni consigliate per i diversi scenari. Quando si copiano dati in un archivio dati basato su file, è consigliabile scrivere in una cartella come file multipli (specificare solo il nome della cartella); in tal caso, le prestazioni risultano migliori rispetto alla scrittura in un singolo file.

Procedure consigliate per il caricamento di dati con opzione partizione:

• Scegliere una colonna distintiva come colonna partizione (ad esempio, chiave primaria o chiave univoca) per evitare l'asimmetria dei dati.
• Se la tabella include una partizione predefinita, usare l'opzione di partizione Partizioni fisiche della tabella per ottenere prestazioni migliori.

Query di esempio per controllare la partizione fisica

SELECT DISTINCT s.name AS SchemaName, t.name AS TableName, pf.name AS PartitionFunctionName, c.name AS ColumnName, iif(pf.name is null, 'no', 'yes') AS HasPartition
FROM sys.tables AS t
LEFT JOIN sys.objects AS o ON t.object_id = o.object_id
LEFT JOIN sys.schemas AS s ON o.schema_id = s.schema_id
LEFT JOIN sys.indexes AS i ON t.object_id = i.object_id 
LEFT JOIN sys.index_columns AS ic ON ic.partition_ordinal > 0 AND ic.index_id = i.index_id AND ic.object_id = t.object_id 
LEFT JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id 
LEFT JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id 
LEFT JOIN sys.partition_functions pf ON pf.function_id = ps.function_id 
WHERE s.name='[your schema]' AND t.name = '[your table name]'

Se la tabella ha una partizione fisica, viene visualizzato "HasPartition" come "sì", come illustrato di seguito.

Tabella di riepilogo

Vedere la tabella seguente per il riepilogo e altre informazioni sull'attività Copy di SQL Server.

Informazioni sull'origine

Informazioni sulla destinazione

Contenuto correlato

• Panoramica di SQL Server