Copiare e trasformare i dati da/in SQL Server usando Azure Data Factory o Azure Synapse Analytics

SI APPLICA A: Azure Data Factory Azure Synapse Analytics

Suggerimento

Provare Data Factory in Microsoft Fabric, una soluzione di analisi all-in-one per le aziende. Microsoft Fabric copre tutto, dallo spostamento dati al data science, all'analisi in tempo reale, alla business intelligence e alla creazione di report. Vedere le informazioni su come iniziare una nuova prova gratuita!

In questo articolo viene illustrato come usare l'attività di copia in pipeline di Azure Data Factory e Azure Synapse Analytics per copiare i dati da/in un database SQL Server e usare Flusso di dati per trasformare o dati in un database SQL Server. Per altre informazioni, vedere l'articolo introduttivo per Azure Data Factory o Azure Synapse Analytics.

Funzionalità supportate

Questo connettore SQL Server è supportato per le funzionalità seguenti:

Funzionalità supportate	IR
Attività di copia (origine/sink)	7.3
Flusso di dati per mapping (origine/sink)	1
Attività Lookup	7.3
Attività GetMetadata	7.3
Attività script	7.3
Attività stored procedure	7.3

① Azure Integration Runtime ② Runtime di integrazione self-hosted

Per un elenco degli archivi dati supportati come origini o sink dall'attività di copia, vedere la tabella relativa agli archivi dati supportati.

In particolare, il connettore SQL Server supporta:

• SQL Server versione 2005 e successive.
• La copia dei dati tramite l'autenticazione di SQL o di Windows.
• Come origine, recupero dati tramite una query SQL o una stored procedure. È anche possibile scegliere di eseguire la copia parallela dall'origine di SQL Server. Per informazioni dettagliate, vedere la sezione Copia parallela dal database SQL.
• Come sink, creazione automatica della tabella di destinazione se non esiste in base allo schema di origine; accodamento di dati a una tabella o richiamo di una stored procedure con logica personalizzata durante la copia.

LocalDB di SQL Server Express non è supportato.

Importante

L'origine dati deve supportare il tipo di dati NVARCHAR perché influisce sulla codifica dei dati quando ai dati viene applicata una codifica non universale.

Prerequisiti

Se l'archivio dati si trova all'interno di una rete locale, una rete virtuale di Azure o un cloud privato virtuale di Amazon, è necessario configurare un runtime di integrazione self-hosted per connettersi.

Se l'archivio dati è un servizio dati del cloud gestito, è possibile usare Azure Integration Runtime. Se l'accesso è limitato solo agli indirizzi IP approvati nelle regole del firewall, è possibile aggiungere IP di Azure Integration Runtime nell'elenco Consentiti.

È anche possibile usare la funzionalitàruntime di integrazione della rete virtuale gestita in Azure Data Factory per accedere alla rete locale senza installare e configurare un runtime di integrazione self-hosted.

Per altre informazioni sui meccanismi di sicurezza di rete e sulle opzioni supportate da Data Factory, vedere strategie di accesso ai dati.

Operazioni preliminari

Per eseguire l'attività di copia con una pipeline, è possibile usare uno degli strumenti o SDK seguenti:

• Strumento Copia dati
• Il portale di Azure
• .NET SDK
• SDK di Python
• Azure PowerShell
• API REST
• Modello di Azure Resource Manager

Creare il servizio collegato di SQL Server tramite l’interfaccia utente

Usare la procedura seguente per creare un servizio collegato di SQL Server nell'interfaccia utente del portale di Azure.

1. Passare alla scheda Gestisci nell'area di lavoro di Azure Data Factory o Synapse e selezionare Servizi collegati, quindi fare clic su Nuovo:

   Azure Data Factory

   

   Azure Synapse

   

2. Cercare SQL e selezionare il connettore SQL Server.

   

3. Configurare i dettagli del servizio, testare la connessione e creare il nuovo servizio collegato.

   

Dettagli di configurazione del connettore

Le sezioni seguenti forniscono informazioni dettagliate sulle proprietà usate per definire entità delle pipeline di Data Factory e Synapse specifiche per il connettore del database SQL Server.

Proprietà del servizio collegato

La versione consigliata di SQL Server supporta TLS 1.3. Fare riferimento a questa sezione per aggiornare il servizio collegato di SQL Server se si usa una versione legacy. Per informazioni dettagliate delle proprietà, vedere le sezioni corrispondenti.

• Versione consigliata
• Versione legacy

Suggerimento

Se viene restituito l'errore con codice errore "UserErrorFailedToConnectToSqlServer" e un messaggio come "Il limite di sessioni per il database è XXX ed è stato raggiunto", aggiungere Pooling=false alla stringa di connessione e riprovare.

Versione consigliata

Queste proprietà generiche sono supportate per un servizio collegato di SQL Server quando si applica la versione consigliata:

Proprietà	Descrizione	Richiesto
type	La proprietà type deve essere impostata su SqlServer.	Sì
server	Nome o indirizzo di rete dell'istanza di SQL Server a cui connettersi.	Sì
database	Nome del database.	Sì
authenticationType	Tipo utilizzato per l'autenticazione. I valori consentiti sono SQL (impostazione predefinita), Windows e UserAssignedManagedIdentity (solo per SQL Server in macchine virtuali di Azure). Passare alla sezione relativa all'autenticazione in base a proprietà e prerequisiti specifici.	Sì
alwaysEncryptedSettings	Specificare le informazioni alwaysencryptedsettings necessarie per consentire ad Always Encrypted di proteggere i dati sensibili archiviati in SQL Server usando un'identità gestita o un'entità servizio. Per altre informazioni, vedere l'esempio JSON che segue la tabella e la sezione Uso di Always Encrypted. Se non specificato, l'impostazione predefinita per Always Encrypted è disabilitata.	No
crittografare	Indicare se la crittografia TLS è necessaria per tutti i dati inviati tra client e server. Opzioni: obbligatorio (per true, impostazione predefinita)/opzionale (per false)/strict.	No
trustServerCertificate	Indicare se il canale verrà crittografato bypassando la catena di certificati per convalidare l'attendibilità.	No
hostNameInCertificate	Nome host da usare quando viene convalidato il certificato del server per la connessione. Se non è specificato, per la convalida del certificato viene utilizzato il nome del server.	No
connectVia	Questo runtime di integrazione viene usato per connettersi all'archivio dati. Per altre informazioni, vedere la sezione Prerequisiti. Se non specificato, viene usato il runtime di integrazione di Azure predefinito.	No

Per altre proprietà di connessione, vedere la tabella seguente:

Proprietà	Descrizione	Richiesto
applicationIntent	Il tipo di carico di lavoro dell'applicazione in caso di connessione a un server. I valori consentiti sono ReadOnly e ReadWrite.	No
connectTimeout	Tempo di attesa (in secondi) per una connessione al server prima della conclusione del tentativo e la generazione di un errore.	No
connectRetryCount	Numero di riconnessioni tentate dopo l'identificazione di un errore di connessione inattiva. Il valore deve essere un numero intero compreso tra 0 e 255.	No
connectRetryInterval	Intervallo di tempo, (espresso in secondi) tra ogni tentativo di riconnessione dopo l'identificazione di un errore di connessione inattiva. Il valore deve essere un numero intero compreso tra 1 e 60.	No
loadBalanceTimeout	Tempo minimo (in secondi) in cui la connessione rimane attiva nel pool di connessione prima di essere eliminata definitivamente.	No
commandTimeout	Tempo di attesa predefinito (in secondi) prima della conclusione del tentativo di esecuzione di un comando e della generazione di un errore.	No
integratedSecurity	I valori consentiti sono true o false. Quando si specifica false, indicare se userName e password sono specificati nella connessione. Quando si specifica true, indica se le credenziali dell'account di Windows corrente vengono usate per l'autenticazione.	No
failoverPartner	Nome o indirizzo di rete del server partner a cui connettersi se il server primario è inattivo.	No
maxPoolSize	Numero massimo di connessioni consentite nel pool di connessioni per la connessione specifica.	No
minPoolSize	Numero minimo di connessioni consentite nel pool di connessioni per la connessione specifica.	No
multipleActiveResultSets	I valori consentiti sono true o false. Quando si specifica true, un'applicazione può gestire più insiemi di risultati attivi (MARS). Quando si specifica false, un'applicazione deve prima elaborare o annullare tutti gli insiemi di risultati da un batch per poter eseguire altri batch in tale connessione.	No
multiSubnetFailover	I valori consentiti sono true o false. Se l'applicazione si connette a un gruppo di disponibilità (AG) AlwaysOn in subnet diverse, l'impostazione di questa proprietà su true garantisce una maggiore velocità di rilevamento e connessione al server attualmente attivo.	No
packetSize	Dimensioni in byte dei pacchetti di rete usati per comunicare con un'istanza del server.	No
pooling	I valori consentiti sono true o false. Quando si specifica true, la connessione sarà in pool. Quando si specifica false, la connessione verrà aperta in modo esplicito ogni volta che è richiesta la connessione.	No

Autenticazione SQL

Per usare l’autenticazione SQL, oltre alle proprietà generiche descritte nella sezione precedente, specificare le proprietà seguenti:

Proprietà	Descrizione	Richiesto
userName	Nome utente da usare quando ci si connette al server.	Sì
password	Password per il nome utente. Contrassegnare questo campo come SecureString per archiviarlo in modo sicuro. In alternativa, fare riferimento a un segreto archiviato in Azure Key Vault.	No

Esempio: usare l’autenticazione SQL

{
    "name": "SqlServerLinkedService",
    "properties": {
        "type": "SqlServer",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "SQL",
            "userName": "<user name>",
            "password": {
                "type": "SecureString",
                "value": "<password>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Esempio: usare l'autenticazione di SQL con una password in Azure Key Vault

{
    "name": "SqlServerLinkedService",
    "properties": {
        "type": "SqlServer",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "SQL",
            "userName": "<user name>",
            "password": { 
                "type": "AzureKeyVaultSecret", 
                "store": { 
                    "referenceName": "<Azure Key Vault linked service name>", 
                    "type": "LinkedServiceReference" 
                }, 
                "secretName": "<secretName>" 
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Esempio: usare Always Encrypted

{
    "name": "SqlServerLinkedService",
    "properties": {
        "type": "SqlServer",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "SQL",
            "userName": "<user name>",
            "password": {
                "type": "SecureString",
                "value": "<password>"
            }
        },
        "alwaysEncryptedSettings": {
            "alwaysEncryptedAkvAuthType": "ServicePrincipal",
            "servicePrincipalId": "<service principal id>",
            "servicePrincipalKey": {
                "type": "SecureString",
                "value": "<service principal key>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Autenticazione di Windows

Per usare l’autenticazione di Windows, oltre alle proprietà generiche descritte nella sezione precedente, specificare le proprietà seguenti:

Proprietà	Descrizione	Richiesto
userName	Consente di specificare un nome utente. Esempio: domainname\username.	Sì
password	Specificare una password per l'account utente specificato per nome utente. Contrassegnare questo campo come SecureString per archiviarlo in modo sicuro. In alternativa, fare riferimento a un segreto archiviato in Azure Key Vault.	Sì

Nota

L'autenticazione di Windows non è supportata nel flusso di dati.

Esempio: usare l’autenticazione di Windows

{
    "name": "SqlServerLinkedService",
    "properties": {
        "type": "SqlServer",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "Windows",
            "userName": "<domain\\username>",
            "password": {
                "type": "SecureString",
                "value": "<password>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Esempio: usare l'autenticazione di Windows con una password in Azure Key Vault

{
    "name": "SqlServerLinkedService",
    "properties": {
        "annotations": [],
        "type": "SqlServer",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "Windows",
            "userName": "<domain\\username>",
            "password": {
                "type": "AzureKeyVaultSecret",
                "store": {
                    "referenceName": "<Azure Key Vault linked service name>",
                    "type": "LinkedServiceReference"
                },
                "secretName": "<secretName>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Autenticazione dell'identità gestita assegnata dall'utente

Nota

L'autenticazione dell'identità gestita assegnata dall'utente si applica solo a SQL Server nelle macchine virtuali di Azure.

Una data factory o un'area di lavoro di Synapse può essere associata a un'identità gestita assegnata dall'utente che rappresenta il servizio durante l'autenticazione ad altre risorse in Azure. È possibile usare questa identità gestita per l'autenticazione di SQL Server nelle macchine virtuali di Azure. L'area di lavoro designata factory o Synapse può accedere e copiare dati da o nel database usando questa identità.

Per usare l'autenticazione dell'identità gestita assegnata dall'utente, oltre alle proprietà generiche descritte nella sezione precedente, specificare le proprietà seguenti:

Proprietà	Descrizione	Richiesto
credentials	Specificare l'identità gestita assegnata dall'utente come oggetto credenziale.	Sì

È anche necessario seguire la procedura seguente:

1. Concedere le autorizzazioni per l'identità gestita assegnata dall'utente.

2. Abilitare l'autenticazione di Microsoft Entra per SQL Server nelle macchine virtuali di Azure.

3. Creare utenti di database indipendenti per l'identità gestita assegnata dall'utente. Connettersi al database da o a cui si desidera copiare dati usando strumenti come SQL Server Management Studio, con un'identità di Microsoft Entra che dispone almeno dell'autorizzazione ALTER ANY USER. Eseguire il codice T-SQL seguente:

   CREATE USER [your_resource_name] FROM EXTERNAL PROVIDER;
   

4. Creare una o più identità gestite assegnate dall'utente e concedere le autorizzazioni necessarie per l'identità gestita assegnata dall'utente, come normalmente si fa per utenti SQL e altri utenti. Eseguire il codice seguente. Per altre opzioni, vedere questo documento.

   ALTER ROLE [role name] ADD MEMBER [your_resource_name];
   

5. Assegnare una o più identità gestite assegnate dall'utente alla data factory e creare le credenziali per ogni identità gestita assegnata dall'utente.

6. Configurare un servizio collegato di SQL Server.

Esempio

{
    "name": "SqlServerLinkedService",
    "properties": {
        "type": "SqlServer",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "UserAssignedManagedIdentity",
            "credential": {
                "referenceName": "credential1",
                "type": "CredentialReference"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Versione legacy

Queste proprietà generiche sono supportate per un servizio collegato di SQL Server quando si applica la versione Legacy:

Proprietà	Descrizione	Richiesto
type	La proprietà type deve essere impostata su SqlServer.	Sì
alwaysEncryptedSettings	Specificare le informazioni alwaysencryptedsettings necessarie per consentire ad Always Encrypted di proteggere i dati sensibili archiviati in SQL Server usando un'identità gestita o un'entità servizio. Per altre informazioni, vedere la sezione Uso di Always Encrypted. Se non specificato, l'impostazione predefinita per Always Encrypted è disabilitata.	No
connectVia	Questo runtime di integrazione viene usato per connettersi all'archivio dati. Per altre informazioni, vedere la sezione Prerequisiti. Se non specificato, viene usato il runtime di integrazione di Azure predefinito.	No

Il connettore SQL Server supporta i tipi di autenticazione seguenti. Per informazioni dettagliate, vedere le sezioni corrispondenti.

• Autenticazione di SQL per la versione legacy
• Autenticazione di Windows per la versione legacy

Autenticazione di SQL per la versione legacy

Per usare l’autenticazione SQL, oltre alle proprietà generiche descritte nella sezione precedente, specificare le proprietà seguenti:

Proprietà	Descrizione	Richiesto
connectionString	Specificare le informazioni connectionString necessarie per connettersi al database di SQL Server. Specificare un ID di accesso come nome utente e accertarsi che sia eseguito il mapping del database a cui collegarsi a questo ID di accesso.	Sì
password	Per inserire una password in Azure Key Vault, eseguire lo spostamento forzato dei dati della configurazione password all'esterno della stringa di connessione. Per altre informazioni, vedere Memorizzare credenziali in Azure Key Vault.	No

Autenticazione di Windows per la versione legacy

Per usare l’autenticazione di Windows, oltre alle proprietà generiche descritte nella sezione precedente, specificare le proprietà seguenti:

Proprietà	Descrizione	Richiesto
connectionString	Specificare le informazioni connectionString necessarie per connettersi al database di SQL Server.	Sì
userName	Consente di specificare un nome utente. Esempio: domainname\username.	Sì
password	Specificare una password per l'account utente specificato per nome utente. Contrassegnare questo campo come SecureString per archiviarlo in modo sicuro. In alternativa, fare riferimento a un segreto archiviato in Azure Key Vault.	Sì

Proprietà del set di dati

Per un elenco completo delle sezioni e delle proprietà disponibili per la definizione di set di dati, vedere l'articolo sui set di dati. Questa sezione fornisce un elenco delle proprietà supportate dal set di dati SQL Server.

Per copiare dati da/in un database di SQL Server, sono supportate le proprietà seguenti:

Proprietà	Descrizione	Richiesto
type	La proprietà type del set di dati deve essere impostata su SqlServerTable.	Sì
schema	Nome dello schema.	No per l'origine, Sì per il sink
table	Nome della tabella/vista.	No per l'origine, Sì per il sink
tableName	Nome della tabella/vista con schema. Questa proprietà è supportata per garantire la compatibilità con le versioni precedenti. Per i nuovi carichi di lavoro, usare schema e table.	No per l'origine, Sì per il sink

Esempio

{
    "name": "SQLServerDataset",
    "properties":
    {
        "type": "SqlServerTable",
        "linkedServiceName": {
            "referenceName": "<SQL Server linked service name>",
            "type": "LinkedServiceReference"
        },
        "schema": [ < physical schema, optional, retrievable during authoring > ],
        "typeProperties": {
            "schema": "<schema_name>",
            "table": "<table_name>"
        }
    }
}

Proprietà dell'attività di copia

Per un elenco completo delle sezioni e delle proprietà disponibili per definire le attività, vedere l'articolo sulle pipeline. Questa sezione fornisce un elenco delle proprietà supportate dall'origine e dal sink di SQL Server.

SQL Server come origine

Suggerimento

Per caricare dati da SQL Server in modo efficiente usando il partizionamento dei dati, vedere ulteriori dettagli in Copia parallela da un database SQL.

Per copiare dati da un database SQL Server, impostare il tipo di origine nell'attività di copia su SqlSource. Nella sezione source dell'attività di copia sono supportate le proprietà seguenti:

Proprietà	Descrizione	Richiesto
type	La proprietà type dell'origine dell'attività di copia deve essere impostata su SqlSource.	Sì
sqlReaderQuery	Usare la query SQL personalizzata per leggere i dati. Un esempio è select * from MyTable.	No
sqlReaderStoredProcedureName	Questa proprietà definisce il nome della stored procedure che legge i dati dalla tabella di origine. L'ultima istruzione SQL deve essere un'istruzione SELECT nella stored procedure.	No
storedProcedureParameters	Questi parametri sono relativi alla 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.	No
isolationLevel	Specifica il comportamento di blocco della transazione per l'origine SQL. Valori consentiti: ReadCommitted, ReadUncommitted, RepeatableRead, Serializable e Snapshot. Se non specificato, viene utilizzato il livello di isolamento predefinito del database. Per altre informazioni dettagliate, vedere questo documento.	No
partitionOptions	Specifica le opzioni di partizionamento dei dati usate per caricare dati da SQL Server. Valori consentiti: None (predefinito), PhysicalPartitionsOfTable e DynamicRange. Quando è abilitata un'opzione partizione (diversa da None), il grado di parallelismo per il caricamento simultaneo di dati da SQL Server è controllato dall'impostazione parallelCopies nell'attività di copia.	No
partitionSettings	Specifica il gruppo di impostazioni per il partizionamento dei dati. Applicare quando l'opzione di partizione non è None.	No
In partitionSettings:
partitionColumnName	Specificare il nome della colonna di origine nel tipo integer o data/datetime (int, smallint, bigint, date, smalldatetime, datetime, datetime2 o datetimeoffset) che verrà usato 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. Si applica quando l'opzione di partizione è DynamicRange. 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.	No
partitionUpperBound	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. Si applica quando l'opzione di partizione è DynamicRange. Per un esempio, vedere la sezione Copia parallela dal database SQL.	No
partitionLowerBound	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. Si applica quando l'opzione di partizione è DynamicRange. Per un esempio, vedere la sezione Copia parallela dal database SQL.	No

Tenere presente quanto segue:

• Se è specificato sqlReaderQuery per SqlSource, l’attività di copia esegue questa query nell’origine di SQL Server per ottenere i dati. In alternativa, è possibile specificare una stored procedure indicando i parametri sqlReaderStoredProcedureName e storedProcedureParameters, 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.

Esempio: usare una query SQL

"activities":[
    {
        "name": "CopyFromSQLServer",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<SQL Server input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "SqlSource",
                "sqlReaderQuery": "SELECT * FROM MyTable"
            },
            "sink": {
                "type": "<sink type>"
            }
        }
    }
]

Esempio: usare una stored procedure

"activities":[
    {
        "name": "CopyFromSQLServer",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<SQL Server input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "SqlSource",
                "sqlReaderStoredProcedureName": "CopyTestSrcStoredProcedureWithParameters",
                "storedProcedureParameters": {
                    "stringData": { "value": "str3" },
                    "identifier": { "value": "$$Text.Format('{0:yyyy}', <datetime parameter>)", "type": "Int"}
                }
            },
            "sink": {
                "type": "<sink type>"
            }
        }
    }
]

Definizione della stored procedure

CREATE PROCEDURE CopyTestSrcStoredProcedureWithParameters
(
    @stringData varchar(20),
    @identifier int
)
AS
SET NOCOUNT ON;
BEGIN
    select *
    from dbo.UnitTestSrcTable
    where dbo.UnitTestSrcTable.stringData != stringData
    and dbo.UnitTestSrcTable.identifier != identifier
END
GO

SQL Server come sink

Suggerimento

Per altre informazioni sui comportamenti di scrittura, le configurazioni e le procedure consigliate supportate, vedere Procedura consigliata per il caricamento dei dati in SQL Server.

Per copiare dati da SQL Server, impostare il tipo di sink nell'attività di copia su SqlSink. Nella sezione sink dell'attività di copia sono supportate le proprietà seguenti:

Proprietà	Descrizione	Richiesto
type	La proprietà type del sink dell'attività di copia deve essere impostata su SqlSink.	Sì
preCopyScript	Questa proprietà specifica una query SQL per l'attività di copia da eseguire prima della scrittura dei dati in SQL Server. Viene richiamata solo una volta per ogni esecuzione della copia. È possibile usare questa proprietà per pulire i dati precaricati.	No
tableOption	Specifica se creare automaticamente la tabella sink, se non esistente, in base allo schema di origine. La creazione automatica della tabella non è supportata quando il sink specifica la stored procedure. I valori consentiti sono: none (impostazione predefinita), autoCreate.	No
sqlWriterStoredProcedureName	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à preCopyScript. Vedere l’esempio in Richiamare una stored procedure da un sink SQL.	No
storedProcedureTableTypeParameterName	Nome del parametro del tipo di tabella specificato nella stored procedure.	No
sqlWriterTableType	Tipo di tabella da usare nella stored procedure. Nel corso dell'attività di copia, i dati spostati vengono resi disponibili in una tabella temporanea di questo tipo. Il codice della stored procedure può quindi unire i dati di cui è in corso la copia con i dati esistenti.	No
storedProcedureParameters	Parametri per la stored procedure. I valori consentiti sono coppie nome-valore. I nomi e le maiuscole e minuscole dei parametri devono corrispondere ai nomi e alle maiuscole e minuscole dei parametri della stored procedure.	No
writeBatchSize	Numero di righe da inserire nella tabella SQL per batch. I valori consentiti sono integer per il numero di righe. Per impostazione predefinita, il servizio determina dinamicamente le dimensioni appropriate del batch in base alle dimensioni della riga.	No
writeBatchTimeout	Tempo di attesa per il completamento dell'operazione insert, upsert e stored procedure prima del timeout. I valori consentiti sono relativi all'intervallo di tempo. Esempio: "00:30:00" per 30 minuti. Se non si specifica alcun valore, viene utilizzato il timeout predefinito: "00:30:00".	No
maxConcurrentConnections	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.	No
WriteBehavior	Specificare il comportamento di scrittura per l'attività di copia per caricare dati nel database di SQL Server. Valori consentiti: Insert e Upsert. Per impostazione predefinita, il servizio usa l’operazione insert per caricare dati.	No
upsertSettings	Specificare il gruppo di impostazioni per il comportamento di scrittura. Applicare quando l'opzione WriteBehavior è Upsert.	No
In upsertSettings:
useTempDB	Specificare se utilizzare la tabella temporanea globale o quella fisica come tabella provvisoria per l’operazione upsert. Per impostazione predefinita, il servizio usa una tabella temporanea globale come tabella provvisoria. il valore è true.	No
interimSchemaName	Specificare lo schema provvisorio per la creazione di una tabella provvisoria se viene utilizzata la tabella fisica. Nota: l'utente deve disporre dell'autorizzazione per la creazione e l'eliminazione della tabella. Per impostazione predefinita, la tabella provvisoria condividerà lo stesso schema della tabella sink. Applicare quando l'opzione useTempDB è False.	No
keys	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.	No

Esempio 1: accodare dati

"activities":[
    {
        "name": "CopyToSQLServer",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<SQL Server output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "<source type>"
            },
            "sink": {
                "type": "SqlSink",
                "tableOption": "autoCreate",
                "writeBatchSize": 100000
            }
        }
    }
]

Esempio 2: richiamare una stored procedure durante la copia

Per altre informazioni, vedere Richiamare una stored procedure da un sink SQL.

"activities":[
    {
        "name": "CopyToSQLServer",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<SQL Server output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "<source type>"
            },
            "sink": {
                "type": "SqlSink",
                "sqlWriterStoredProcedureName": "CopyTestStoredProcedureWithParameters",
                "storedProcedureTableTypeParameterName": "MyTable",
                "sqlWriterTableType": "MyTableType",
                "storedProcedureParameters": {
                    "identifier": { "value": "1", "type": "Int" },
                    "stringData": { "value": "str1" }
                }
            }
        }
    }
]

Esempio 3: Upsert dei dati

"activities":[
    {
        "name": "CopyToSQLServer",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<SQL Server output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "<source type>"
            },
            "sink": {
                "type": "SqlSink",
                "tableOption": "autoCreate",
                "writeBehavior": "upsert",
                "upsertSettings": {
                    "useTempDB": true,
                    "keys": [
                        "<column name>"
                    ]
                },
            }
        }
    }
]

Copia parallela dal database SQL

Il connettore SQL Server nell'attività di copia 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à di copia.

Quando si abilita la copia partizionata, l’attività di copia esegue query parallele sull'origine di SQL Server per caricare i dati in base alle partizioni. Il grado di parallelismo è controllato dall'impostazione parallelCopies sull'attività di copia. Se, ad esempio, si imposta parallelCopies su quattro, il servizio simultaneamente genera ed esegue quattro query in base all'opzione partizione specificata e alle impostazioni, e ogni query recupera una porzione di dati dal database di 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.

Scenario	Impostazioni consigliate
Caricamento completo da una tabella di grandi dimensioni, con partizioni fisiche.	Opzione di partizione: partizioni fisiche della tabella. Durante l'esecuzione, il servizio rileva automaticamente le partizioni fisiche e copia i dati in base alle partizioni. Per controllare se la tabella contenga o meno una partizione fisica, è possibile fare riferimento a questa query.
Caricamento completo da una tabella di grandi dimensioni, senza partizioni fisiche, con una colonna integer o datetime per il partizionamento dei dati.	Opzioni di partizione: partizione a intervalli dinamici. Colonna partizione (facoltativo): specificare la colonna usata per il partizionamento dei dati. Se non è specificato, viene usata la colonna della chiave primaria. Limite superiore partizione e limite inferiore partizione (facoltativo): specificare se si desidera determinare lo stride della partizione. Non si tratta di filtrare le righe nella tabella; tutte le righe della tabella verranno partizionate e copiate. Se non è specificato, l'attività di copia rileva automaticamente i valori e può richiedere molto tempo a seconda dei valori MIN e MAX. È preferibile specificare un limite superiore e un limite inferiore. Ad esempio, se “ID” della colonna partizione include valori compresi tra 1 e 100 e si imposta come limite inferiore 20 e come limite superiore 80, con copia parallela 4, il servizio recupera i dati in base a 4 partizioni - ID nell'intervallo < = 20, [21, 50], [51, 80] e > = 81 rispettivamente.
Caricamento di notevoli quantità di dati utilizzando una query personalizzata, senza partizioni fisiche, con una colonna integer o date/datetime per il partizionamento dei dati.	Opzioni di partizione: partizione a intervalli dinamici. Query: SELECT * FROM <TableName> WHERE ?DfDynamicRangePartitionCondition AND <your_additional_where_clause>. Colonna di partizione: specificare la colonna usata per il partizionamento dei dati. Limite superiore partizione e limite inferiore partizione (facoltativo): specificare se si desidera determinare lo stride della partizione. Ciò non è utile a filtrare le righe nella tabella; tutte le righe del risultato della query verranno partizionate e copiate. Se non specificato, l'attività Copy rileva automaticamente il valore. Ad esempio, se la colonna di partizione "ID" include valori compresi tra 1 e 100 e si imposta il limite inferiore su 20 e il limite superiore su 80, con copia parallela come 4 il servizio recupera i dati per 4 partizioni - ID nell'intervallo <=20, [21, 50], [51, 80], e >=81, rispettivamente. Di seguito sono riportate altre query di esempio per diversi scenari: 1. Eseguire una query sull'intera tabella: SELECT * FROM <TableName> WHERE ?DfDynamicRangePartitionCondition 2. Eseguire una query da una tabella con selezione colonne e filtri aggiuntivi per la clausola where: SELECT <column_list> FROM <TableName> WHERE ?DfDynamicRangePartitionCondition AND <your_additional_where_clause> 3. Query con sottoquery: SELECT <column_list> FROM (<your_sub_query>) AS T WHERE ?DfDynamicRangePartitionCondition AND <your_additional_where_clause> 4. Query con partizione nella sottoquery: SELECT <column_list> FROM (SELECT <your_sub_query_column_list> FROM <TableName> WHERE ?DfDynamicRangePartitionCondition) AS T

Procedure consigliate per il caricamento di dati con opzione partizione:

1. Scegliere una colonna distintiva come colonna partizione (ad esempio, chiave primaria o chiave univoca) per evitare l'asimmetria dei dati.
2. Se la tabella include una partizione predefinita, usare l'opzione di partizione "Partizioni fisiche della tabella" per ottenere prestazioni migliori.
3. Se si usa Azure Integration Runtime per copiare i dati, è possibile impostare "Unità di integrazione dati (DIU)" (>4) perché utilizzi più risorse di calcolo. Controllare gli scenari applicabili.
4. “Grado di parallelismo copia” controlla i numeri partizione; se si imposta per questo numero un valore troppo grande, le prestazioni potrebbero talvolta risentirne. È preferibile impostare questo numero come (DIU o numero di nodi del runtime di integrazione self-hosted) * (2-4).

Esempio: caricamento completo da una tabella di grandi dimensioni con partizioni fisiche

"source": {
    "type": "SqlSource",
    "partitionOption": "PhysicalPartitionsOfTable"
}

Esempio: query con partizione a intervalli dinamici

"source": {
    "type": "SqlSource",
    "query": "SELECT * FROM <TableName> WHERE ?DfDynamicRangePartitionCondition AND <your_additional_where_clause>",
    "partitionOption": "DynamicRange",
    "partitionSettings": {
        "partitionColumnName": "<partition_column_name>",
        "partitionUpperBound": "<upper_value_of_partition_column (optional) to decide the partition stride, not as data filter>",
        "partitionLowerBound": "<lower_value_of_partition_column (optional) to decide the partition stride, not as data filter>"
    }
}

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.

Procedura consigliata per il caricamento dei dati in SQL Server

Quando si copiano dati in SQL Server, potrebbe essere necessario un comportamento di scrittura diverso:

• Accodamento: i dati di origine hanno solo nuovi record.
• Upsert: i dati di origine hanno sia inserimenti che aggiornamenti.
• Sovrascrittura: si desidera ricaricare ogni volta l'intera tabella dimensioni.
• Scrittura con logica personalizzata: è necessaria un'elaborazione aggiuntiva prima dell'inserimento finale nella tabella di destinazione.

Per informazioni sulla modalità di configurazione e per procedure consigliate, vedere le sezioni pertinenti.

Accodare dati

L'accodamento dei dati è il comportamento predefinito di questo connettore sink di SQL Server. Il servizio esegue un inserimento bulk per scrivere nella tabella in modo efficiente. È possibile configurare opportunamente l'origine e il sink nell'attività di copia.

Eseguire l'upsert dei dati

L'attività di copia ora supporta il caricamento nativo dei dati in una tabella temporanea del database e quindi l'aggiornamento dei dati nella tabella sink, se la chiave esiste, altrimenti inserisce nuovi dati. Per altre informazioni sulle impostazioni dell’operazione upsert nelle attività di copia, vedere SQL Server come sink.

Sovrascrivere l'intera tabella

È possibile configurare la proprietà preCopyScript in un sink dell'attività di copia. In questo caso, per ogni attività di copia eseguita, il servizio esegue prima lo script. Quindi, esegue la copia per inserire i dati. Ad esempio, per sovrascrivere l'intera tabella con i dati più recenti, specificare uno script per eliminare tutti i record prima del caricamento bulk dei nuovi dati dall'origine.

Scrivere dati con logica personalizzata

I passaggi per scrivere dati con una logica personalizzata sono simili a quelli descritti nella sezione Upsert dei dati. Quando si necessita di applicare un'elaborazione aggiuntiva prima dell'inserimento finale dei dati di origine nella tabella di destinazione, è possibile caricare in una tabella di staging e quindi richiamare un'attività stored procedure, o richiamare una stored procedure nel sink dell'attività di copia per applicare i dati.

Richiamare una stored procedure da un sink SQL

Quando si copiano dati in un database SQL Server, è anche possibile configurare e richiamare una stored procedure specificata dall'utente con parametri aggiuntivi in ogni batch della tabella di origine. La funzionalità di stored procedure sfrutta i parametri valutati a livello di tabella. Tenere presente che il servizio esegue automaticamente il wrapping della stored procedure nella propria transazione, pertanto qualunque transazione creata all'interno della stored procedure diventerà una transazione nidificata e potrebbe avere implicazioni per la gestione delle eccezioni.

È possibile usare una stored procedure quando non si possono usare i meccanismi di copia predefiniti. Ad esempio, quando si desidera applicare un'elaborazione aggiuntiva prima dell'inserimento finale dei dati di origine nella tabella di destinazione. Alcuni esempi di elaborazione aggiuntivi sono: quando si desidera unire colonne, cercare valori aggiuntivi e inserire in più tabelle.

Nell'esempio seguente viene illustrato come usare una stored procedure per eseguire un'operazione upsert in una tabella del database SQL Server. Si presuppone che i dati di input e la tabella Marketing del sink abbiano tre colonne: ProfileID, Stato e Categoria. Eseguire l'operazione upsert nella colonna ProfileID e applicarla solo a una categoria specifica denominata “ProductA”.

1. Nel database, definire il tipo di tabella con lo stesso nome di sqlWriterTableType. Lo schema del tipo di tabella è identico allo schema restituito dai dati di input.

   CREATE TYPE [dbo].[MarketingType] AS TABLE(
       [ProfileID] [varchar](256) NOT NULL,
       [State] [varchar](256) NOT NULL,
       [Category] [varchar](256) NOT NULL
   )
   

2. Nel database, definire la stored procedure con lo stesso nome di sqlWriterStoredProcedureName. che gestisce i dati di input dell'origine specificata e li unisce nella tabella di output. Il nome del parametro del tipo di tabella nella stored procedure deve essere identico al valore tableName definito nel set di dati.

   CREATE PROCEDURE spOverwriteMarketing @Marketing [dbo].[MarketingType] READONLY, @category varchar(256)
   AS
   BEGIN
   MERGE [dbo].[Marketing] AS target
   USING @Marketing AS source
   ON (target.ProfileID = source.ProfileID and target.Category = @category)
   WHEN MATCHED THEN
       UPDATE SET State = source.State
   WHEN NOT MATCHED THEN
       INSERT (ProfileID, State, Category)
       VALUES (source.ProfileID, source.State, source.Category);
   END
   

3. Definire la sezione SqlSink nel file JSON dell'attività di copia come indicato di seguito:

   "sink": {
       "type": "SqlSink",
       "sqlWriterStoredProcedureName": "spOverwriteMarketing",
       "storedProcedureTableTypeParameterName": "Marketing",
       "sqlWriterTableType": "MarketingType",
       "storedProcedureParameters": {
           "category": {
               "value": "ProductA"
           }
       }
   }
   

Proprietà del flusso di dati per mapping

Durante la trasformazione dei dati in un flusso di dati per mapping, è possibile leggere e scrivere in tabelle del database di SQL Server. Per altre informazioni, vedere la trasformazione origine e la trasformazione sink nei flussi di dati per mapping.

Nota

Per accedere a un’istanza locale di SQL Server, è necessario usare l'area di lavoroRete virtuale gestita di Azure Data Factory o Synapse usando un endpoint privato. Per i passaggi dettagliati, fare riferimento a questa esercitazione.

Trasformazione origine

Nella tabella seguente sono elencate le proprietà supportate dall'origine di SQL Server. È possibile modificare queste proprietà nella scheda Opzioni origine.

Nome	Descrizione	Richiesto	Valori consentiti	Proprietà script del flusso di dati
Tabella	Se si seleziona Tabella come input, il flusso di dati recupera tutti i dati dalla tabella specificata nel set di dati.	No	-	-
Query	Se si seleziona Query come input, specificare una query SQL per recuperare i dati dall'origine, il che esegue l'override di ogni tabella specificata nel set di dati. L'uso delle query è un ottimo metodo per ridurre il numero di righe per test o ricerche. La clausola Order By non è supportata, ma è possibile impostare un'istruzione SELECT FROM completa. È possibile usare anche funzioni di tabella definite dall'utente. select * from udfGetData() è una funzione definita dall'utente in SQL che restituisce una tabella che è possibile usare nel flusso di dati. Esempio di query: Select * from MyTable where customerId > 1000 and customerId < 2000	No	String	query
Dimensioni del batch	Specificare una dimensione batch per suddividere i dati di grandi dimensioni in letture.	No	Intero	batchSize
Livello di isolamento	Scegliere uno dei livelli di isolamento seguenti: - Read Committed - Read Uncommitted (impostazione predefinita) - Repeatable Read - Serializable - None (livello di isolamento ignorato)	No	READ_COMMITTED READ_UNCOMMITTED REPEATABLE_READ SERIALIZABLE NONE	isolationLevel
Abilita l’estrazione incrementale	Usare questa opzione per indicare ad ADF di elaborare solo le righe modificate dall'ultima esecuzione della pipeline.	No	-	-
Colonna data incrementale	Quando si usa la funzionalità di estrazione incrementale, è necessario scegliere la colonna date/datetime da usare come limite nella tabella di origine.	No	-	-
Abilita Change Data Capture nativo (anteprima)	Usare questa opzione per indicare ad ADF di elaborare solo i dati differenziali acquisiti tramite la tecnologia Change Data Capture di SQL dall'ultima esecuzione della pipeline. Con questa opzione, i dati delta che includono inserimento, aggiornamento ed eliminazione di righe verranno caricati automaticamente senza necessità di una colonna data incrementale. Per usare questa opzione in ADF, è necessario prima abilitare Change Data Capture in SQL Server. Per altre informazioni su questa opzione in ADF, vedere Cange Data Capture nativo.	No	-	-
Iniziare a leggere dall'inizio	L'impostazione di questa opzione con l'estrazione incrementale indicherà ad ADF di leggere tutte le righe alla prima esecuzione di una pipeline con l'estrazione incrementale attivata.	No	-	-

Suggerimento

L’espressione di tabella comune (CTE) in SQL non è supportata nella modalità query del flusso di dati per mapping, poiché il prerequisito dell'uso di questa modalità consiste nel fatto che le query possono essere usate nella clausola FROM della query SQL, ma non è possibile eseguire questa operazione con CTE. L’uso di CTE richiede la creazione di una stored procedure tramite la query seguente:

CREATE PROC CTESP @query nvarchar(max)
AS
BEGIN
EXECUTE sp_executesql @query;
END

Usare quindi la modalità Stored procedure nella trasformazione di origine del flusso di dati di mapping e impostare @query come l'esempio with CTE as (select 'test' as a) select * from CTE. A questo punto, l’uso di CTE è possibile come previsto.

Esempio di script di origine di SQL Server

Quando si usa SQL Server come tipo di origine, lo script del flusso di dati associato è:

source(allowSchemaDrift: true,
    validateSchema: false,
    isolationLevel: 'READ_UNCOMMITTED',
    query: 'select * from MYTABLE',
    format: 'query') ~> SQLSource

Trasformazione sink

Nella tabella seguente sono elencate le proprietà supportate dal sink di SQL Server. È possibile modificare queste proprietà nella scheda Opzioni sink.

Nome	Descrizione	Richiesto	Valori consentiti	Proprietà script del flusso di dati
Metodo di aggiornamento	Specificare le operazioni consentite nella destinazione del database. Per impostazione predefinita, sono consentiti solo gli inserimenti. Per operazioni di aggiornamento, upsert o eliminazione di righe, è necessaria una trasformazione Altera riga perché i tag siano applicati alle righe per queste azioni.	Sì	true oppure false	deletable insertable updateable upsertable
Colonne chiave	Per operazioni di aggiornamento, upsert ed eliminazione, è necessario impostare una o più colonne chiave per determinare quale riga modificare. Il nome della colonna selezionato come chiave verrà usato come parte dell'operazione di aggiornamento, upsert ed eliminazione successiva. Pertanto, è necessario selezionare una colonna esistente nel mapping sink.	No	Matrice	keys
Ignora scrittura colonne chiave	Se si desidera non scrivere il valore nella colonna chiave, selezionare "Ignora la scrittura di colonne chiave".	No	true oppure false	skipKeyWrites
azione Tabella	determina se ricreare o rimuovere tutte le righe dalla tabella di destinazione prima della scrittura. - Nessuno: non verrà eseguita alcuna azione sulla tabella. - Ricrea: la tabella verrà eliminata e ricreata. Questa opzione è obbligatoria se si crea una nuova tabella in modo dinamico. - Tronca: verranno rimosse tutte le righe della tabella di destinazione.	No	true oppure false	recreate truncate
Dimensioni del batch	Specificare il numero di righe scritte in ogni batch. Dimensioni batch più grandi migliorano l'ottimizzazione della compressione e della memoria, ma rischiano di causare eccezioni di memoria insufficiente durante la memorizzazione nella cache dei dati.	No	Intero	batchSize
Pre e post-script SQL	Specificare script SQL a più righe che verranno eseguiti prima (pre-elaborazione) e dopo (post-elaborazione) la scrittura dei dati nel database sink.	No	String	preSQLs postSQLs

Suggerimento

1. È consigliabile suddividere singoli script batch con più comandi in più batch.
2. Possono essere eseguite come parte di un batch solo le istruzioni DDL (Data Definition Language) e DML (Data Manipulation Language) che restituiscono un semplice conteggio di aggiornamento. Altre informazioni in Esecuzione di operazioni batch

Esempio di script di sink di SQL Server

Quando si usa SQL Server come tipo di sink, lo script del flusso di dati associato è:

IncomingStream sink(allowSchemaDrift: true,
    validateSchema: false,
    deletable:false,
    insertable:true,
    updateable:true,
    upsertable:true,
    keys:['keyColumn'],
    format: 'table',
    skipDuplicateMapInputs: true,
    skipDuplicateMapOutputs: true) ~> SQLSink

Mapping dei tipi di dati per SQL Server

Quando si copiano dati da/in SQL Server, vengono usati i mapping seguenti da tipi di dati di SQL Server in tipi di dati provvisori di Azure Data Factory. Le pipeline di Synapse, che implementano Data Factory, usano gli stessi mapping. Vedere Mapping dello schema e del tipo di dati per informazioni su come l'attività di copia esegue il mapping dello schema di origine e del tipo di dati al sink.

Tipo di dati di SQL Server	Tipo di dati provvisorio di Data Factory
bigint	Int64
binary	Byte[]
bit	Booleano
char	String, Char[]
data	DataOra
Datetime	DataOra
datetime2	Data/Ora
Datetimeoffset	DateTimeOffset
Decimale	Decimale
FILESTREAM attribute (varbinary(max))	Byte[]
Float	Double
image	Byte[]
int	Int32
money	Decimale
nchar	String, Char[]
ntext	String, Char[]
numeric	Decimale
nvarchar	String, Char[]
real	Singola
rowversion	Byte[]
smalldatetime	Data/Ora
smallint	Int16
smallmoney	Decimale
sql_variant	Object
Testo	String, Char[]
Ora	TimeSpan
timestamp	Byte[]
tinyint	Int16
uniqueidentifier	GUID
varbinary	Byte[]
varchar	String, Char[]
xml	String

Nota

Per tipi di dati con mapping al tipo provvisorio Decimal, attualmente l’attività Copy supporta la precisione fino a 28. Se si hanno dati che richiedono una precisione maggiore di 28, è consigliabile convertirli in una stringa in una query SQL.

Quando si copiano dati da SQL Server usando Azure Data Factory, viene eseguito il mapping del tipo di dati bit al tipo di dati provvisori Boolean. Se esistono dati che devono essere mantenuti come tipo di dati bit, usare le query con T-SQL CAST o CONVERT.

Proprietà dell'attività Lookup

Per altre informazioni sulle proprietà, vedere Attività Lookup.

Proprietà dell'attività GetMetadata

Per altre informazioni sulle proprietà, vedere Attività GetMetadata

Uso di Always Encrypted

Quando si copiano dati da/in SQL Server con Always Encrypted, seguire questa procedura:

1. Memorizzare la chiave master della colonna (CMK) in un Azure Key Vault. Altre informazioni su come configurare Always Encrypted usando Azure Key Vault

2. Accertarsi di concedere l'accesso all'insieme di credenziali delle chiavi in cui è archiviata la chiave master della colonna (CMK). Per le autorizzazioni necessarie, fare riferimento a questo articolo.

3. Creare un servizio collegato per connettersi al database SQL e abilitare la funzione "Always Encrypted" usando l'identità gestita o l'entità servizio.

Nota

SQL Server Always Encrypted supporta gli scenari seguenti:

1. Gli archivi dati di origine o sink usano un'identità gestita o un'entità servizio come tipo di autenticazione del provider di chiavi.
2. Gli archivi dati di origine e sink usano l'identità gestita come tipo di autenticazione del provider di chiavi.
3. Gli archivi dati di origine e sink usano la stessa entità servizio del tipo di autenticazione del provider di chiavi.

Nota

Attualmente, SQL Server Always Encrypted è supportato solo per la trasformazione dell'origine in flussi di dati per mapping.

Change Data Capture nativo

Azure Data Factory può supportare funzionalità Change Data Capture native per SQL Server, il database di Azure SQL e l'istanza gestita di Azure SQL. I dati modificati, inclusi l’inserimento, l’aggiornamento e l’eliminazione di righe in archivi SQL possono essere rilevati ed estratti automaticamente dal flusso di dati per mapping di ADF. Senza alcuna esperienza di coding nel flusso di dati per mapping, gli utenti possono ottenere facilmente uno scenario di replica dei dati da archivi SQL accodando un database come archivio di destinazione. Inoltre, gli utenti possono anche comporre qualsiasi logica di trasformazione dati intermedia per ottenere uno scenario ETL incrementale dagli archivi SQL.

Accertarsi di mantenere invariato il nome della pipeline e dell'attività, in modo che il checkpoint possa essere registrato da ADF per ottenere automaticamente i dati modificati dall'ultima esecuzione. Se si modifica il nome della pipeline o il nome dell'attività, il checkpoint verrà reimpostato, il che porta a ricominciare dall'inizio o a ottenere le modifiche da subito nella successiva esecuzione. Se si desidera modificare il nome della pipeline o il nome dell'attività mantenendo comunque il checkpoint per ottenere automaticamente i dati modificati dall'ultima esecuzione, usare la propria chiave checkpoint nell'attività del flusso di dati per ottenere tale risultato.

Quando si esegue il debug della pipeline, questa funzionalità funziona allo stesso modo. Tenere presente che il checkpoint verrà reimpostato quando si aggiorna il browser durante l'esecuzione del debug. Quando il risultato della pipeline dall'esecuzione del debug è soddisfacente, è possibile procedere alla pubblicazione e all'attivazione della pipeline. Al momento della prima attivazione della pipeline pubblicata, la pipeline viene riavviata automaticamente dall'inizio o ottiene le modifiche da tale momento in avanti.

Nella sezione di monitoraggio, è sempre possibile eseguire nuovamente una pipeline. Quando si esegue questa operazione, i dati modificati vengono sempre acquisiti dal checkpoint precedente dell'esecuzione della pipeline selezionata.

Esempio 1:

Quando si concatena direttamente una trasformazione di origine che fa riferimento al set di dati abilitato per SQL CDC, con una trasformazione sink che fa riferimento a un database in un flusso di dati per mapping, le modifiche apportate all'origine SQL verranno applicate automaticamente al database di destinazione, in modo da ottenere facilmente uno scenario di replica dei dati tra database. È possibile usare il metodo di aggiornamento nella trasformazione sink per selezionare se si desideri consentire l'inserimento, l'aggiornamento o l'eliminazione nel database di destinazione. Lo script di esempio nel flusso di dati di mapping è il seguente.

source(output(
		id as integer,
		name as string
	),
	allowSchemaDrift: true,
	validateSchema: false,
	enableNativeCdc: true,
	netChanges: true,
	skipInitialLoad: false,
	isolationLevel: 'READ_UNCOMMITTED',
	format: 'table') ~> source1
source1 sink(allowSchemaDrift: true,
	validateSchema: false,
	deletable:true,
	insertable:true,
	updateable:true,
	upsertable:true,
	keys:['id'],
	format: 'table',
	skipDuplicateMapInputs: true,
	skipDuplicateMapOutputs: true,
	errorHandlingOption: 'stopOnFirstError') ~> sink1

Esempio 2:

Se si desidera abilitare lo scenario ETL anziché la replica dei dati tra database tramite SQL CDC, è possibile usare espressioni nel flusso di dati per mapping isInsert(1), isUpdate(1) e isDelete(1) per distinguere le righe con tipi di operazione diversi. Di seguito è riportato uno degli script di esempio per il flusso di dati per mapping sulla derivazione di una colonna con il valore: 1 per indicare le righe inserite, 2 per indicare le righe aggiornate e 3 per indicare le righe eliminate per le trasformazioni downstream per l’elaborazione dei dati delta.

source(output(
		id as integer,
		name as string
	),
	allowSchemaDrift: true,
	validateSchema: false,
	enableNativeCdc: true,
	netChanges: true,
	skipInitialLoad: false,
	isolationLevel: 'READ_UNCOMMITTED',
	format: 'table') ~> source1
source1 derive(operationType = iif(isInsert(1), 1, iif(isUpdate(1), 2, 3))) ~> derivedColumn1
derivedColumn1 sink(allowSchemaDrift: true,
	validateSchema: false,
	skipDuplicateMapInputs: true,
	skipDuplicateMapOutputs: true) ~> sink1

Limitazione nota:

• Solo net changes da SQL CDC verrà caricato da ADF tramite cdc.fn_cdc_get_net_changes_.

Risolvere i problemi di connessione

1. Configurare l’istanza di SQL Server per accettare connessioni remote. Avviare SQL Server Management Studio, fare clic con il pulsante destro del mouse su server e selezionare Proprietà. Selezionare Connessioni dall'elenco, quindi selezionare la casella di controllo Consenti connessioni remote a questo server.

   

   Per la procedura dettagliata, vedere Configurare l'opzione di configurazione del server di accesso remoto.

2. Avviare Gestione configurazione SQL Server. Espandere Configurazione di rete SQL Server per l'istanza prevista e selezionare Protocolli per MSSQLSERVER. I protocolli vengono visualizzati nel riquadro destro. Abilitare TCP/IP facendo clic con il pulsante destro del mouse su TCP/IP e selezionando Abilita.

   

   Per informazioni dettagliate e modi alternativi per abilitare il protocollo TCP/IP, vedere Abilitare o disabilitare un protocollo di rete del server.

3. Nella stessa finestra, fare doppio clic su TCP/IP per aprire la finestra Proprietà TCP/IP.

4. Passare alla scheda Indirizzi IP . Scorrere in basso per vedere la sezione IPAll. Prendere nota della Porta TCP. Il valore predefinito è 1433.

5. Creare una regola per Windows Firewall nel computer per consentire il traffico in ingresso attraverso questa porta.

6. Verifica connessione: per connettersi al server SQL usando un nome completo, usare SQL Server Management Studio da un computer diverso. Un esempio è "<machine>.<domain>.corp.<company>.com,1433".

Aggiornare la versione di SQL Server

Per aggiornare la versione di SQL Server, nella pagina Modifica servizio collegato, selezionare Consigliato in Versione e configurare il servizio collegato facendo riferimento a Proprietà del servizio collegato per la versione consigliata.

Differenze tra la versione consigliata e la versione legacy

La tabella seguente illustra le differenze tra SQL Server usando la versione consigliata e la versione legacy.

Versione consigliata	Versione legacy
Supportare TLS 1.3 tramite encrypt come strict.	TLS 1.3 non è supportato.

Contenuto correlato

Per un elenco degli archivi dati supportati come origini e sink dall'attività di copia, vedere Archivi dati supportati.