Copiare dati da o nel database SQL di Azure tramite Azure Data FactoryCopy data to or from Azure SQL Database by using Azure Data Factory

Questo articolo illustra come copiare dati da e verso il database SQL di Azure.This article outlines how to copy data to and from Azure SQL Database. Per altre informazioni su Azure Data Factory, vedere l'articolo introduttivo.To learn about Azure Data Factory, read the introductory article.

Funzionalità supportateSupported capabilities

Questo connettore del database SQL di Azure è supportato per le attività seguenti:This Azure SQL Database connector is supported for the following activities:

In particolare, il connettore del database SQL di Azure supporta queste funzioni:Specifically, this Azure SQL Database connector supports these functions:

  • Copia dei dati usando l'autenticazione SQL e l'autenticazione del token dell'applicazione Azure Active Directory (Azure AD) con un'entità servizio o identità gestite per le risorse di Azure.Copying data by using SQL authentication and Azure Active Directory (Azure AD) Application token authentication with a service principal or managed identities for Azure resources.
  • Come origine, il recupero dei dati tramite una query SQL o un stored procedure.As a source, retrieving data by using a SQL query or a stored procedure.
  • Come sink, l'aggiunta di dati a una tabella di destinazione o la chiamata di un stored procedure con la logica personalizzata durante la copia.As a sink, appending data to a destination table or invoking a stored procedure with custom logic during the copy.

Nota

Il Always Encrypted del database SQL di Azure non è attualmente supportato da questo connettore.Azure SQL Database Always Encrypted isn't supported by this connector now. Per aggirare il contenuto, è possibile usare un connettore ODBC generico e un driver ODBC SQL Server tramite un runtime di integrazione self-hosted.To work around, you can use a generic ODBC connector and a SQL Server ODBC driver via a self-hosted integration runtime. Attenersi a queste linee guida per il download del driver ODBC e le configurazioni della stringa di connessione.Follow this guidance with ODBC driver download and connection string configurations.

Importante

Se si copiano i dati usando Azure Data Factory Integration Runtime, configurare un firewall di azure SQL Server in modo che i servizi di Azure possano accedere al server.If you copy data by using the Azure Data Factory integration runtime, configure an Azure SQL Server firewall so that Azure services can access the server. Se si copiano i dati usando un runtime di integrazione self-hosted, configurare il firewall di Azure SQL Server per consentire l'intervallo di indirizzi IP appropriato.If you copy data by using a self-hosted integration runtime, configure the Azure SQL Server firewall to allow the appropriate IP range. Questo intervallo include l'IP del computer usato per connettersi al database SQL di Azure.This range includes the machine's IP that's used to connect to Azure SQL Database.

Attività inizialiGet started

È possibile usare l'attività di copia con una pipeline tramite uno degli strumenti o degli SDK seguenti.You can use one of the following tools or SDKs to use the copy activity with a pipeline. Per istruzioni dettagliate, selezionare un collegamento:Select a link for step-by-step instructions:

Le sezioni seguenti riportano informazioni dettagliate sulle proprietà che vengono usate per definire Azure Data Factory entità specifiche di un connettore del database SQL di Azure.The following sections provide details about properties that are used to define Azure Data Factory entities specific to an Azure SQL Database connector.

Proprietà del servizio collegatoLinked service properties

Per un servizio collegato al database SQL di Azure sono supportate queste proprietà:These properties are supported for an Azure SQL Database linked service:

ProprietàProperty DescrizioneDescription ObbligatoriaRequired
typetype La proprietà type deve essere impostata su AzureSqlDatabase.The type property must be set to AzureSqlDatabase. Yes
connectionStringconnectionString Specifica le informazioni necessarie per connettersi all'istanza del database SQL di Azure per la proprietà connectionString.Specify information needed to connect to the Azure SQL Database instance for the connectionString property.
Contrassegnare questo campo come SecureString per archiviarlo in modo sicuro in Azure Data Factory.Mark this field as SecureString to store it securely in Azure Data Factory. È anche possibile inserire una password o una chiave dell'entità servizio in Azure Key Vault.You also can put a password or service principal key in Azure Key Vault. Se si tratta dell'autenticazione SQL, estrarre password la configurazione dalla stringa di connessione.If it's SQL authentication, pull the password configuration out of the connection string. Per ulteriori informazioni, vedere l'esempio JSON che segue la tabella e archivia le credenziali in Azure Key Vault.For more information, see the JSON example following the table and Store credentials in Azure Key Vault.
Yes
servicePrincipalIdservicePrincipalId Specificare l'ID client dell'applicazione.Specify the application's client ID. Sì, quando si usa l'autenticazione Azure AD con un'entità servizioYes, when you use Azure AD authentication with a service principal
servicePrincipalKeyservicePrincipalKey Specificare la chiave dell'applicazione.Specify the application's key. Contrassegnare questo campo come SecureString per archiviarlo in modo sicuro in Azure Data Factory o fare riferimento a un segreto archiviato nel Azure Key Vault.Mark this field as SecureString to store it securely in Azure Data Factory or reference a secret stored in Azure Key Vault. Sì, quando si usa l'autenticazione Azure AD con un'entità servizioYes, when you use Azure AD authentication with a service principal
tenanttenant Specificare le informazioni sul tenant, ad esempio il nome di dominio o l'ID tenant, in cui risiede l'applicazione.Specify the tenant information, like the domain name or tenant ID, under which your application resides. Recuperarlo posizionando il puntatore del mouse nell'angolo in alto a destra della portale di Azure.Retrieve it by hovering the mouse in the upper-right corner of the Azure portal. Sì, quando si usa l'autenticazione Azure AD con un'entità servizioYes, when you use Azure AD authentication with a service principal
connectViaconnectVia Questo runtime di integrazione viene usato per connettersi all'archivio dati.This integration runtime is used to connect to the data store. Se l'archivio dati si trova in una rete privata, è possibile usare il runtime di integrazione di Azure o un runtime di integrazione self-hosted.You can use the Azure integration runtime or a self-hosted integration runtime if your data store is located in a private network. Se non specificato, viene usato il runtime di integrazione di Azure predefinito.If not specified, the default Azure integration runtime is used. NoNo

Per altri tipi di autenticazione, fare riferimento alle sezioni seguenti relative, rispettivamente, ai prerequisiti e agli esempi JSON:For different authentication types, refer to the following sections on prerequisites and JSON samples, respectively:

Suggerimento

Se viene raggiunto un errore con il codice di errore "UserErrorFailedToConnectToSqlServer" e un messaggio simile a "il limite della sessione per il database è xxx ed è stato raggiunto Pooling=false ", aggiungere alla stringa di connessione e riprovare.If you hit an error with the error code "UserErrorFailedToConnectToSqlServer" and a message like "The session limit for the database is XXX and has been reached," add Pooling=false to your connection string and try again.

Autenticazione in SQLSQL authentication

Esempio di servizio collegato tramite l'autenticazione SQLLinked service example that uses SQL authentication

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "connectionString": {
                "type": "SecureString",
                "value": "Server=tcp:<servername>.database.windows.net,1433;Database=<databasename>;User ID=<username>@<servername>;Password=<password>;Trusted_Connection=False;Encrypt=True;Connection Timeout=30"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Password in Azure Key VaultPassword in Azure Key Vault

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "connectionString": {
                "type": "SecureString",
                "value": "Server=tcp:<servername>.database.windows.net,1433;Database=<databasename>;User ID=<username>@<servername>;Trusted_Connection=False;Encrypt=True;Connection Timeout=30"
            },
            "password": { 
                "type": "AzureKeyVaultSecret", 
                "store": { 
                    "referenceName": "<Azure Key Vault linked service name>", 
                    "type": "LinkedServiceReference" 
                }, 
                "secretName": "<secretName>" 
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Autenticazione di un'entità servizioService principal authentication

Per usare l'autenticazione token dell'applicazione di Azure AD basata sull'entità servizio, seguire questa procedura:To use a service principal-based Azure AD application token authentication, follow these steps:

  1. Creare un'applicazione Azure Active Directory dalla portale di Azure.Create an Azure Active Directory application from the Azure portal. Prendere nota del nome dell'applicazione e dei valori seguenti che definiscono il servizio collegato:Make note of the application name and the following values that define the linked service:

    • ID applicazioneApplication ID
    • Chiave applicazioneApplication key
    • ID tenantTenant ID
  2. Effettuare il provisioning di un amministratore Azure Active Directory per il SQL Server di Azure nel portale di Azure, se non è già stato fatto.Provision an Azure Active Directory administrator for your Azure SQL Server on the Azure portal if you haven't already done so. L'amministratore di Azure AD deve essere un utente o un gruppo di Azure AD, ma non può essere un'entità servizio.The Azure AD administrator must be an Azure AD user or Azure AD group, but it can't be a service principal. Questo passaggio viene eseguito in modo che, nel passaggio successivo, sia possibile usare un'identità di Azure AD per creare un utente di database indipendente per l'entità servizio.This step is done so that, in the next step, you can use an Azure AD identity to create a contained database user for the service principal.

  3. Creare utenti di database indipendente per l'entità servizio.Create contained database users for the service principal. Connettersi al database da o a cui si vogliono copiare i dati usando strumenti come SQL Server Management Studio, con un'identità Azure AD con almeno l'autorizzazione ALTER ANY USER.Connect to the database from or to which you want to copy data by using tools like SQL Server Management Studio, with an Azure AD identity that has at least ALTER ANY USER permission. Eseguire il T-SQL seguente:Run the following T-SQL:

    CREATE USER [your application name] FROM EXTERNAL PROVIDER;
    
  4. Concedere all'entità servizio le autorizzazioni necessarie come si fa normalmente per gli utenti SQL o per altri.Grant the service principal needed permissions as you normally do for SQL users or others. Eseguire il codice seguente.Run the following code. Per altre opzioni, vedere questo documento.For more options, see this document.

    EXEC sp_addrolemember [role name], [your application name];
    
  5. Configurare un servizio collegato del database SQL di Azure in Azure Data Factory.Configure an Azure SQL Database linked service in Azure Data Factory.

Esempio di servizio collegato tramite l'autenticazione basata su entità servizioLinked service example that uses service principal authentication

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "connectionString": {
                "type": "SecureString",
                "value": "Server=tcp:<servername>.database.windows.net,1433;Database=<databasename>;Connection Timeout=30"
            },
            "servicePrincipalId": "<service principal id>",
            "servicePrincipalKey": {
                "type": "SecureString",
                "value": "<service principal key>"
            },
            "tenant": "<tenant info, e.g. microsoft.onmicrosoft.com>"
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Autenticazione di identità gestite per le risorse di AzureManaged identities for Azure resources authentication

Una data factory può essere associata a un'identità gestita per le risorse di Azure, che rappresenta la data factory specifica.A data factory can be associated with a managed identity for Azure resources that represents the specific data factory. È possibile usare questa identità gestita per l'autenticazione del database SQL di Azure.You can use this managed identity for Azure SQL Database authentication. La factory designata può accedere ai dati e copiarli da o verso il database tramite questa identità.The designated factory can access and copy data from or to your database by using this identity.

Per utilizzare l'autenticazione di identità gestita, attenersi alla seguente procedura.To use managed identity authentication, follow these steps.

  1. Effettuare il provisioning di un amministratore Azure Active Directory per il SQL Server di Azure nel portale di Azure, se non è già stato fatto.Provision an Azure Active Directory administrator for your Azure SQL Server on the Azure portal if you haven't already done so. L'amministratore Azure AD può essere un utente Azure AD o un gruppo di Azure AD.The Azure AD administrator can be an Azure AD user or an Azure AD group. Se si concede al gruppo un'identità gestita un ruolo di amministratore, ignorare i passaggi 3 e 4.If you grant the group with managed identity an admin role, skip steps 3 and 4. L'amministratore dispone di accesso completo al database.The administrator has full access to the database.

  2. Creare utenti di database indipendente per l'identità gestita del Azure Data Factory.Create contained database users for the Azure Data Factory managed identity. Connettersi al database da o a cui si vogliono copiare i dati usando strumenti come SQL Server Management Studio, con un'identità Azure AD con almeno l'autorizzazione ALTER ANY USER.Connect to the database from or to which you want to copy data by using tools like SQL Server Management Studio, with an Azure AD identity that has at least ALTER ANY USER permission. Eseguire il T-SQL seguente:Run the following T-SQL:

    CREATE USER [your Data Factory name] FROM EXTERNAL PROVIDER;
    
  3. Concedere le autorizzazioni necessarie per la Data Factory identità gestita come si fa normalmente per gli utenti SQL e altri.Grant the Data Factory managed identity needed permissions as you normally do for SQL users and others. Eseguire il codice seguente.Run the following code. Per altre opzioni, vedere questo documento.For more options, see this document.

    EXEC sp_addrolemember [role name], [your Data Factory name];
    
  4. Configurare un servizio collegato del database SQL di Azure in Azure Data Factory.Configure an Azure SQL Database linked service in Azure Data Factory.

EsempioExample

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "connectionString": {
                "type": "SecureString",
                "value": "Server=tcp:<servername>.database.windows.net,1433;Database=<databasename>;Connection Timeout=30"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Proprietà del set di datiDataset properties

Per un elenco completo delle sezioni e delle proprietà disponibili per la definizione dei set di impostazioni, vedere Datasets.For a full list of sections and properties available to define datasets, see Datasets. Questa sezione presenta un elenco delle proprietà supportate dal set di dati del database SQL di Azure.This section provides a list of properties supported by the Azure SQL Database dataset.

Per copiare dati da o nel database SQL di Azure, sono supportate le proprietà seguenti:To copy data from or to Azure SQL Database, the following properties are supported:

ProprietàProperty DescrizioneDescription ObbligatoriaRequired
typetype La proprietà type del set di dati deve essere impostata su AzureSqlTable.The type property of the dataset must be set to AzureSqlTable. YesYes
schemaschema Nome dello schema.Name of the schema. No per l'origine, Sì per il sinkNo for source, Yes for sink
tabletable Nome della tabella o della vista.Name of the table/view. No per l'origine, Sì per il sinkNo for source, Yes for sink
tableNametableName Nome della tabella o della vista con schema.Name of the table/view with schema. Questa proprietà è supportata per compatibilità con le versioni precedenti.This property is supported for backward compatibility. Per il nuovo carico di schema lavoro table, utilizzare e.For new workload, use schema and table. No per l'origine, Sì per il sinkNo for source, Yes for sink

Esempio di proprietà dei set di datiDataset properties example

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

Proprietà dell'attività di copiaCopy activity properties

Per un elenco completo delle sezioni e delle proprietà disponibili per la definizione delle attività, vedere l'articolo sulle pipeline.For a full list of sections and properties available for defining activities, see Pipelines. Questa sezione presenta un elenco delle proprietà supportate dall'origine e dal sink del database SQL di Azure.This section provides a list of properties supported by the Azure SQL Database source and sink.

Database SQL di Azure come origineAzure SQL Database as the source

Per copiare dati dal database SQL di Azure, nella sezione origine dell'attività di copia sono supportate le proprietà seguenti:To copy data from Azure SQL Database, the following properties are supported in the copy activity source section:

ProprietàProperty DescrizioneDescription ObbligatoriaRequired
typetype La proprietà Type dell'origine dell'attività di copia deve essere impostata su AzureSqlSource.The type property of the copy activity source must be set to AzureSqlSource. Il tipo "sqlSource" è ancora supportato per la compatibilità con le versioni precedenti."SqlSource" type is still supported for backward compatibility. Yes
sqlReaderQuerysqlReaderQuery Questa proprietà usa la query SQL personalizzata per leggere i dati.This property uses the custom SQL query to read data. Un esempio è select * from MyTable.An example is select * from MyTable. NoNo
sqlReaderStoredProcedureNamesqlReaderStoredProcedureName Nome della stored procedure che legge i dati dalla tabella di origine.The name of the stored procedure that reads data from the source table. L'ultima istruzione SQL deve essere un'istruzione SELECT nella stored procedure.The last SQL statement must be a SELECT statement in the stored procedure. NoNo
storedProcedureParametersstoredProcedureParameters Parametri per la stored procedure.Parameters for the stored procedure.
I valori consentiti sono coppie nome-valore.Allowed values are name or value pairs. I nomi e le maiuscole e minuscole dei parametri devono corrispondere ai nomi e alle maiuscole e minuscole dei parametri stored procedure.The names and casing of parameters must match the names and casing of the stored procedure parameters.
NoNo

Punti da notare:Points to note:

  • Se viene specificato sqlReaderQuery per AzureSqlSource, l'attività di copia esegue questa query nell'origine del database SQL di Azure per ottenere i dati.If sqlReaderQuery is specified for AzureSqlSource, the copy activity runs this query against the Azure SQL Database source to get the data. In alternativa, è possibile specificare una stored procedure indicando i parametri sqlReaderStoredProcedureName e storedProcedureParameters, se la stored procedure accetta parametri.You also can specify a stored procedure by specifying sqlReaderStoredProcedureName and storedProcedureParameters if the stored procedure takes parameters.
  • Se non si specifica sqlReaderQuery o sqlReaderStoredProcedureName, le colonne definite nella sezione "Structure" del set di dati JSON vengono usate per creare una query.If you don't specify either sqlReaderQuery or sqlReaderStoredProcedureName, the columns defined in the "structure" section of the dataset JSON are used to construct a query. La query select column1, column2 from mytable viene eseguita nel database SQL di Azure.The query select column1, column2 from mytable runs against Azure SQL Database. Se la definizione del set di dati non include "structure", vengono selezionate tutte le colonne della tabella.If the dataset definition doesn't have "structure," all columns are selected from the table.

Esempio di query SQLSQL query example

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

Esempio di stored procedureStored procedure example

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

Definizione della stored procedureStored procedure definition

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

Database SQL di Azure come sinkAzure SQL Database as the sink

Suggerimento

Scopri di più sui comportamenti di scrittura supportati, sulle configurazioni e sulle procedure consigliate, dalla procedura consigliata per il caricamento dei dati nel database SQL di Azure.Learn more about the supported write behaviors, configurations, and best practices from Best practice for loading data into Azure SQL Database.

Per copiare i dati nel database SQL di Azure, nella sezione sink dell'attività di copia sono supportate le proprietà seguenti:To copy data to Azure SQL Database, the following properties are supported in the copy activity sink section:

ProprietàProperty DescrizioneDescription ObbligatoriaRequired
typetype La proprietà Type del sink dell'attività di copia deve essere impostata su AzureSqlSink.The type property of the copy activity sink must be set to AzureSqlSink. Il tipo "sqlsink" è ancora supportato per la compatibilità con le versioni precedenti."SqlSink" type is still supported for backward compatibility. Yes
writeBatchSizewriteBatchSize Numero di righe da inserire nella tabella SQL per batch.Number of rows to insert into the SQL table per batch.
Il valore consentito è integer (numero di righe).The allowed value is integer (number of rows). Per impostazione predefinita, Azure Data Factory determina in modo dinamico le dimensioni del batch appropriate in base alle dimensioni della riga.By default, Azure Data Factory dynamically determines the appropriate batch size based on the row size.
NoNo
writeBatchTimeoutwriteBatchTimeout Tempo di attesa per il completamento dell'operazione di inserimento batch prima del timeout.The wait time for the batch insert operation to finish before it times out.
Il valore consentito è timespan.The allowed value is timespan. Un esempio è "00:30:00" (30 minuti).An example is “00:30:00” (30 minutes).
NoNo
preCopyScriptpreCopyScript Specificare una query SQL per l'attività di copia da eseguire prima di scrivere i dati nel database SQL di Azure.Specify a SQL query for the copy activity to run before writing data into Azure SQL Database. Viene richiamata solo una volta per ogni esecuzione della copia.It's invoked only once per copy run. Usare questa proprietà per pulire i dati precaricati.Use this property to clean up the preloaded data. NoNo
sqlWriterStoredProcedureNamesqlWriterStoredProcedureName Il nome della stored procedure che definisce come applicare i dati di origine in una tabella di destinazione.The name of the stored procedure that defines how to apply source data into a target table.
Questa stored procedure viene richiamata per batch.This stored procedure is invoked per batch. Per le operazioni che vengono eseguite una sola volta e non hanno nulla a che fare con i dati di origine, ad esempio delete preCopyScript o TRUNCATE, usare la proprietà.For operations that run only once and have nothing to do with source data, for example, delete or truncate, use the preCopyScript property.
NoNo
storedProcedureTableTypeParameterNamestoredProcedureTableTypeParameterName Nome del parametro del tipo di tabella specificato nell'stored procedure.The parameter name of the table type specified in the stored procedure. NoNo
sqlWriterTableTypesqlWriterTableType Nome del tipo di tabella da utilizzare nel stored procedure.The table type name to be used in the stored procedure. Nel corso dell'attività di copia, i dati spostati vengono resi disponibili in una tabella temporanea di questo tipo.The copy activity makes the data being moved available in a temp table with this table type. Il codice della stored procedure può quindi unire i dati di cui è in corso la copia con i dati esistenti.Stored procedure code can then merge the data that's being copied with existing data. NoNo
storedProcedureParametersstoredProcedureParameters Parametri per la stored procedure.Parameters for the stored procedure.
I valori consentiti sono coppie nome-valore.Allowed values are name and value pairs. I nomi e le maiuscole e minuscole dei parametri devono corrispondere ai nomi e alle maiuscole e minuscole dei parametri della stored procedure.Names and casing of parameters must match the names and casing of the stored procedure parameters.
NoNo
tableOptiontableOption Specifica se creare automaticamente la tabella di sink se non esiste in base allo schema di origine.Specifies whether to automatically create the sink table if not exists based on the source schema. La creazione automatica della tabella non è supportata quando il sink specifica stored procedure o la copia temporanea è configurata nell'attività di copia.Auto table creation is not supported when sink specifies stored procedure or staged copy is configured in copy activity. I valori consentiti sono: none (impostazione predefinita),. autoCreateAllowed values are: none (default), autoCreate. NoNo
disableMetricsCollectiondisableMetricsCollection Data Factory raccoglie le metriche, ad esempio il database SQL di Azure DTU per l'ottimizzazione delle prestazioni di copia e le raccomandazioni.Data Factory collects metrics such as Azure SQL Database DTUs for copy performance optimization and recommendations. Se si è interessati a questo comportamento, specificare true per disattivarlo.If you are concerned with this behavior, specify true to turn it off. No (il valore predefinito è false)No (default is false)

Esempio 1: Accodare datiExample 1: Append data

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

Esempio 2: Richiama un stored procedure durante la copiaExample 2: Invoke a stored procedure during copy

Per altre informazioni, vedere Richiamare una stored procedure da un sink SQL.Learn more details from Invoke a stored procedure from a SQL sink.

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

Procedura consigliata per il caricamento dei dati nel database SQL di AzureBest practice for loading data into Azure SQL Database

Quando si copiano dati nel database SQL di Azure, potrebbe essere necessario un comportamento di scrittura diverso:When you copy data into Azure SQL Database, you might require different write behavior:

  • Accoda: I dati di origine hanno solo nuovi record.Append: My source data has only new records.
  • Upsert: I dati di origine sono inserimenti e aggiornamenti.Upsert: My source data has both inserts and updates.
  • Sovrascrivi: Si desidera ricaricare ogni volta un'intera tabella delle dimensioni.Overwrite: I want to reload an entire dimension table each time.
  • Scrivi con logica personalizzata: Sono necessarie altre operazioni di elaborazione prima dell'inserimento finale nella tabella di destinazione.Write with custom logic: I need extra processing before the final insertion into the destination table.

Vedere le rispettive sezioni sulla configurazione di in Azure Data Factory e procedure consigliate.Refer to the respective sections about how to configure in Azure Data Factory and best practices.

Accoda datiAppend data

L'accodamento dei dati è il comportamento predefinito del connettore di sink del database SQL di Azure.Appending data is the default behavior of this Azure SQL Database sink connector. Azure Data Factory esegue un inserimento bulk per scrivere in modo efficiente nella tabella.Azure Data Factory does a bulk insert to write to your table efficiently. È possibile configurare l'origine e il sink di conseguenza nell'attività di copia.You can configure the source and sink accordingly in the copy activity.

Eseguire l'upsert dei datiUpsert data

Opzione 1: Quando si dispone di una grande quantità di dati da copiare, usare l'approccio seguente per eseguire una Upsert:Option 1: When you have a large amount of data to copy, use the following approach to do an upsert:

  • Utilizzare innanzitutto una tabella temporanea con ambito database per eseguire il caricamento bulk di tutti i record utilizzando l'attività di copia.First, use a database scoped temporary table to bulk load all records by using the copy activity. Poiché le operazioni sulle tabelle temporanee con ambito database non vengono registrate, è possibile caricare milioni di record in pochi secondi.Because operations against database scoped temporary tables aren't logged, you can load millions of records in seconds.
  • Eseguire un'attività stored procedure in Azure Data Factory per applicare un'istruzione merge o Insert/Update.Run a stored procedure activity in Azure Data Factory to apply a MERGE or INSERT/UPDATE statement. Utilizzare la tabella temp come origine per eseguire tutti gli aggiornamenti o gli inserimenti come una singola transazione.Use the temp table as the source to perform all updates or inserts as a single transaction. In questo modo, il numero di round trip e di operazioni di log è ridotto.In this way, the number of round trips and log operations is reduced. Alla fine dell'attività stored procedure, è possibile troncare la tabella temporanea in modo che sia pronta per il ciclo Upsert successivo.At the end of the stored procedure activity, the temp table can be truncated to be ready for the next upsert cycle.

In Azure Data Factory, ad esempio, è possibile creare una pipeline con un' attività di copia concatenata a un' attività di stored procedure.As an example, in Azure Data Factory, you can create a pipeline with a Copy activity chained with a Stored Procedure activity. Il primo copia i dati dall'archivio di origine in una tabella temporanea del database SQL di Azure, ad esempio # #UpsertTempTable, come nome della tabella nel set di dati.The former copies data from your source store into an Azure SQL Database temporary table, for example, ##UpsertTempTable, as the table name in the dataset. Il secondo richiama quindi un stored procedure per unire i dati di origine dalla tabella temporanea alla tabella di destinazione e pulire la tabella temporanea.Then the latter invokes a stored procedure to merge source data from the temp table into the target table and clean up the temp table.

Upsert

Nel database definire un stored procedure con la logica di MERGE, come nell'esempio seguente, a cui fa riferimento l'attività stored procedure precedente.In your database, define a stored procedure with MERGE logic, like the following example, which is pointed to from the previous stored procedure activity. Si supponga che la destinazione sia la tabella Marketing con tre colonne: ProfileID, State e Category.Assume that the target is the Marketing table with three columns: ProfileID, State, and Category. Eseguire Upsert in base alla colonna profileid .Do the upsert based on the ProfileID column.

CREATE PROCEDURE [dbo].[spMergeData]
AS
BEGIN
    MERGE TargetTable AS target
    USING ##UpsertTempTable AS source
    ON (target.[ProfileID] = source.[ProfileID])
    WHEN MATCHED THEN
        UPDATE SET State = source.State
    WHEN NOT matched THEN
        INSERT ([ProfileID], [State], [Category])
      VALUES (source.ProfileID, source.State, source.Category);
    
    TRUNCATE TABLE ##UpsertTempTable
END

Opzione 2: È anche possibile scegliere di richiamare un stored procedure all'interno dell'attività di copia.Option 2: You also can choose to invoke a stored procedure within the copy activity. Questo approccio esegue ogni riga della tabella di origine anziché usare BULK INSERT come approccio predefinito nell'attività di copia, che non è appropriato per Upsert su larga scala.This approach runs each row in the source table instead of using bulk insert as the default approach in the copy activity, which isn't appropriate for large-scale upsert.

Sovrascrivi l'intera tabellaOverwrite the entire table

È possibile configurare la proprietà preCopyScript nel sink dell'attività di copia.You can configure the preCopyScript property in the copy activity sink. In questo caso, per ogni attività di copia eseguita, Azure Data Factory esegue prima lo script.In this case, for each copy activity that runs, Azure Data Factory runs the script first. Esegue quindi la copia per inserire i dati.Then it runs the copy to insert the data. Per sovrascrivere l'intera tabella con i dati più recenti, ad esempio, specificare uno script per eliminare prima tutti i record prima di eseguire il caricamento bulk dei nuovi dati dall'origine.For example, to overwrite the entire table with the latest data, specify a script to first delete all the records before you bulk load the new data from the source.

Scrivere dati con logica personalizzataWrite data with custom logic

I passaggi per scrivere i dati con la logica personalizzata sono simili a quelli descritti nella sezione relativa ai dati di Upsert .The steps to write data with custom logic are similar to those described in the Upsert data section. Quando è necessario applicare un'elaborazione aggiuntiva prima dell'inserimento finale dei dati di origine nella tabella di destinazione, per la scalabilità elevata è possibile eseguire una delle due operazioni seguenti:When you need to apply extra processing before the final insertion of source data into the destination table, for large scale, you can do one of two things:

  • Caricare in una tabella temporanea con ambito database e quindi richiamare un stored procedure.Load to a database scoped temporary table and then invoke a stored procedure.
  • Richiama un stored procedure durante la copia.Invoke a stored procedure during copy.

Richiamare una stored procedure da un sink SQLInvoke a stored procedure from a SQL sink

Quando si copiano dati nel database SQL di Azure, è anche possibile configurare e richiamare un stored procedure specificato dall'utente con parametri aggiuntivi.When you copy data into Azure SQL Database, you also can configure and invoke a user-specified stored procedure with additional parameters. La funzionalità di stored procedure sfrutta i parametri valutati a livello di tabella.The stored procedure feature takes advantage of table-valued parameters.

Suggerimento

La chiamata di un stored procedure elabora i dati riga per riga anziché usando un'operazione bulk, che non è consigliabile per la copia su larga scala.Invoking a stored procedure processes the data row by row instead of by using a bulk operation, which we don't recommend for large-scale copy. Per altre informazioni, vedere la procedura consigliata per il caricamento dei dati nel database SQL di Azure.Learn more from Best practice for loading data into Azure SQL Database.

È possibile usare una stored procedure quando non si possono usare i meccanismi di copia predefiniti.You can use a stored procedure when built-in copy mechanisms don't serve the purpose. Un esempio è quando si desidera applicare un'ulteriore elaborazione prima dell'inserimento finale dei dati di origine nella tabella di destinazione.An example is when you want to apply extra processing before the final insertion of source data into the destination table. Alcuni esempi di elaborazione aggiuntivi sono quando si desidera unire le colonne, cercare valori aggiuntivi e inserirli in più di una tabella.Some extra processing examples are when you want to merge columns, look up additional values, and insert into more than one table.

L'esempio seguente illustra come usare una stored procedure per eseguire un'operazione di upsert in una tabella del database SQL di Azure.The following sample shows how to use a stored procedure to do an upsert into a table in Azure SQL Database. Si supponga che i dati di input e la tabella di Marketing sink includano tre colonne: ProfileID, State e Category.Assume that the input data and the sink Marketing table each have three columns: ProfileID, State, and Category. Eseguire Upsert in base alla colonna profileid e applicarla solo a una categoria specifica denominata "producta".Do the upsert based on the ProfileID column, and only apply it for a specific category called "ProductA".

  1. Nel database definire il tipo di tabella con lo stesso nome di sqlWriterTableType.In your database, define the table type with the same name as sqlWriterTableType. Lo schema del tipo di tabella è identico allo schema restituito dai dati di input.The schema of the table type is the same as the schema returned by your input data.

    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.In your database, define the stored procedure with the same name as SqlWriterStoredProcedureName. che gestisce i dati di input dell'origine specificata e li unisce nella tabella di output.It handles input data from your specified source and merges into the output table. Il nome del parametro del tipo di tabella nella stored procedure corrisponde a TableName definito nel set di dati.The parameter name of the table type in the stored procedure is the same as tableName defined in the dataset.

    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. In Azure Data Factory definire la sezione del sink SQL nell'attività di copia come indicato di seguito:In Azure Data Factory, define the SQL sink section in the copy activity as follows:

    "sink": {
        "type": "AzureSqlSink",
        "SqlWriterStoredProcedureName": "spOverwriteMarketing",
        "storedProcedureTableTypeParameterName": "Marketing",
        "SqlWriterTableType": "MarketingType",
        "storedProcedureParameters": {
            "category": {
                "value": "ProductA"
            }
        }
    }
    

Mapping delle proprietà del flusso di datiMapping data flow properties

Per informazioni dettagliate, vedere trasformazione origine e trasformazione sink nel flusso di dati del mapping.Learn details from source transformation and sink transformation in mapping data flow.

Mapping dei tipi di dati per il database SQL di AzureData type mapping for Azure SQL Database

Quando i dati vengono copiati da o nel database SQL di Azure, i mapping seguenti vengono usati dai tipi di dati del database SQL di Azure per Azure Data Factory tipi di dati provvisori.When data is copied from or to Azure SQL Database, the following mappings are used from Azure SQL Database data types to Azure Data Factory interim data types. 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.To learn how the copy activity maps the source schema and data type to the sink, see Schema and data type mappings.

Tipi di dati del database SQL di AzureAzure SQL Database data type Tipo di dati provvisorio di Azure Data FactoryAzure Data Factory interim data type
bigintbigint Int64Int64
binarybinary Byte[]Byte[]
bitbit BooleanBoolean
charchar String, Char[]String, Char[]
datedate DateTimeDateTime
DatetimeDatetime DateTimeDateTime
datetime2datetime2 DateTimeDateTime
DatetimeoffsetDatetimeoffset DateTimeOffsetDateTimeOffset
DecimalDecimal DecimalDecimal
FILESTREAM attribute (varbinary(max))FILESTREAM attribute (varbinary(max)) Byte[]Byte[]
FloatFloat DoubleDouble
imageimage Byte[]Byte[]
intint Int32Int32
moneymoney DecimalDecimal
ncharnchar String, Char[]String, Char[]
ntextntext String, Char[]String, Char[]
numericnumeric DecimalDecimal
nvarcharnvarchar String, Char[]String, Char[]
realreal SingleSingle
rowversionrowversion Byte[]Byte[]
smalldatetimesmalldatetime DateTimeDateTime
smallintsmallint Int16Int16
smallmoneysmallmoney DecimalDecimal
sql_variantsql_variant ObjectObject
texttext String, Char[]String, Char[]
timetime TimeSpanTimeSpan
timestamptimestamp Byte[]Byte[]
tinyinttinyint ByteByte
uniqueidentifieruniqueidentifier GuidGuid
varbinaryvarbinary Byte[]Byte[]
varcharvarchar String, Char[]String, Char[]
xmlxml XmlXml

Nota

Per i tipi di dati associati al tipo provvisorio Decimal, Azure Data Factory supporta attualmente la precisione fino a 28.For data types that map to the Decimal interim type, currently Azure Data Factory supports precision up to 28. Se i dati hanno una precisione maggiore di 28, provare a eseguire la conversione in una stringa nella query SQL.If you have data with precision larger than 28, consider converting to a string in SQL query.

Proprietà attività di ricercaLookup activity properties

Per informazioni dettagliate sulle proprietà, controllare l' attività di ricerca.To learn details about the properties, check Lookup activity.

Proprietà dell'attività GetMetadataGetMetadata activity properties

Per informazioni dettagliate sulle proprietà, controllare l' attività GetMetadataTo learn details about the properties, check GetMetadata activity

Passaggi successiviNext steps

Per un elenco di archivi dati supportati come origini e sink dall'attività di copia in Azure Data Factory, vedere archivi dati e formati supportati.For a list of data stores supported as sources and sinks by the copy activity in Azure Data Factory, see Supported data stores and formats.