Copia y transformación de datos en SQL Server mediante Azure Data Factory o Azure Synapse Analytics

Article
06/26/2024

SE APLICA A: Azure Data Factory Azure Synapse Analytics

Sugerencia

Pruebe Data Factory en Microsoft Fabric, una solución de análisis todo en uno para empresas. Microsoft Fabric abarca todo, desde el movimiento de datos hasta la ciencia de datos, el análisis en tiempo real, la inteligencia empresarial y los informes. Obtenga información sobre cómo iniciar una nueva evaluación gratuita.

En este artículo se describe cómo usar la actividad de copia de las canalizaciones de Azure Data Factory y Azure Synapse para copiar datos con la base de datos de SQL Server como origen o destino y cómo usar Data Flow para transformar los datos de la base de datos de SQL Server. Para obtener más información, lea el artículo de introducción para Azure Data Factory o Azure Synapse Analytics.

Funcionalidades admitidas

Este conector SQL Server es compatible con las funcionalidades siguientes:

Funcionalidades admitidas	IR
Actividad de copia (origen/receptor)	① ②
Flujo de datos de asignación (origen/receptor)	①
Actividad de búsqueda	① ②
Actividad GetMetadata	① ②
Actividad de script	① ②
Actividad de procedimiento almacenado	① ②

① Azure Integration Runtime ② Entorno de ejecución de integración autohospedado

Consulte la tabla de almacenes de datos compatibles para ver una lista de almacenes de datos que la actividad de copia admite como orígenes o receptores.

En concreto, este conector SQL Server admite las siguientes funcionalidades:

SQL Server versión 2005 y posteriores.
La copia de datos con autenticación de SQL o Windows.
Como origen, la recuperación de datos mediante una consulta SQL o un procedimiento almacenado. También puede optar por la copia en paralelo desde un origen de SQL Server, consulte la sección Copia en paralelo desde una base de datos SQL para obtener detalles.
Como receptor, la creación automática de la tabla de destino si no existe en función del esquema de origen; la anexión de datos a una tabla o la invocación de un procedimiento almacenado con lógica personalizada durante la copia.

No se admite la base de datos local LocalDB de SQL Server Express.

Importante

El origen de datos debe admitir el tipo de datos NVARCHAR, ya que afecta a la codificación de los datos cuando se les aplica una codificación no universal.

Requisitos previos

Si el almacén de datos se encuentra en una red local, una red virtual de Azure o una nube privada virtual de Amazon, debe configurar un entorno de ejecución de integración autohospedado para conectarse a él.

Si el almacén de datos es un servicio de datos en la nube administrado, puede usar Azure Integration Runtime. Si el acceso está restringido a las direcciones IP que están aprobadas en las reglas de firewall, puede agregar direcciones IP de Azure Integration Runtime a la lista de permitidos.

También puede usar la característica del entorno de ejecución de integración de red virtual administrada de Azure Data Factory para acceder a la red local sin instalar ni configurar un entorno de ejecución de integración autohospedado.

Consulte Estrategias de acceso a datos para más información sobre los mecanismos de seguridad de red y las opciones que admite Data Factory.

Introducción

Para realizar la actividad de copia con una canalización, puede usar una de los siguientes herramientas o SDK:

Creación de un servicio vinculado de SQL Server mediante la interfaz de usuario

Siga estos pasos para crear un servicio vinculado de SQL Server en la interfaz de usuario de Azure Portal.

Vaya a la pestaña Administrar del área de trabajo de Azure Data Factory o Synapse y seleccione Servicios vinculados; luego haga clic en Nuevo:
- Azure Data Factory
- Azure Synapse
Busque SQL y seleccione el conector de SQL Server.
Configure los detalles del servicio, pruebe la conexión y cree el nuevo servicio vinculado.

Detalles de configuración del conector

En las secciones siguientes se proporcionan detalles sobre las propiedades que se usan para definir entidades de canalizacione sde Data Factory y SQL Server específicas del conector de base de datos SQL Server.

Propiedades del servicio vinculado

La versión Recomendada de SQL Server admite TLS 1.3. Consulta esta sección para actualizar el servicio vinculado de SQL Server si usas la versión Heredada. Para obtener los detalles de la propiedad, consulta las secciones correspondientes.

Versión recomendada
Versión heredada

Sugerencia

Si recibió un error con el código de error "UserErrorFailedToConnectToSqlServer" y un mensaje parecido a "The session limit for the database is XXX and has been reached" (El límite de sesión de la base de datos es XXX y ya se ha alcanzado), agregue Pooling=false a la cadena de conexión e inténtelo de nuevo.

Versión recomendada

Estas propiedades genéricas son compatibles con un servicio vinculado de SQL Server cuando se aplica la versión Recomendada:

Propiedad	Descripción	Obligatorio
type	La propiedad type se debe establecer en: SqlServer.	Sí
server	Nombre o dirección de red de la instancia de SQL Server a la que desea conectarse.	Sí
database	El nombre de la base de datos.	Sí
authenticationType	Tipo usado para la autenticación. Los valores permitidos son SQL (valor predeterminado), Windows y UserAssignedManagedIdentity (solo para SQL Server en máquinas virtuales de Azure). Ve a la sección de autenticación pertinente sobre propiedades y requisitos previos específicos.	Sí
alwaysEncryptedSettings	Especifique la información alwaysencryptedsettings necesaria para permitir que Always Encrypted proteja los datos confidenciales almacenados en un servidor SQL mediante una identidad administrada o una entidad de servicio. Para obtener más información, vea el ejemplo de JSON debajo de la tabla y consulte la sección Uso de Always Encrypted. Si no se especifica, la configuración predeterminada Always Encrypted está deshabilitada.	No
encrypt	Indica si se requiere cifrado TLS para todos los datos enviados entre el cliente y el servidor. Opciones: obligatorio (para true, valor predeterminado)/opcional (para falso)/restringido.	No
trustServerCertificate	Indique si el canal se cifrará al pasar la cadena de certificados para validar la confianza.	No
hostNameInCertificate	Nombre de host que se va a usar al validar el certificado de servidor para la conexión. Cuando no se especifica, el nombre del servidor se usa para la validación de certificados.	No
connectVia	Este entorno de ejecución de integración se usa para conectarse al almacén de datos. Obtenga más información en la sección Requisitos previos. Si no se especifica, se usa el valor predeterminado de Azure Integration Runtime.	No

Para obtener más propiedades de conexión, consulte la tabla siguiente:

Propiedad	Descripción	Obligatorio
applicationIntent	Tipo de carga de trabajo de aplicación al conectarse a un servidor. Los valores permitidos son `ReadOnly` y `ReadWrite`.	No
connectTimeout	El período de tiempo (en segundos) que se espera a una conexión al servidor antes de finalizar el intento y generar un error.	No
connectRetryCount	Número de reconexión intentadas después de identificar un error de conexión inactiva. El valor debe ser un número entero entre 0 y 255.	No
connectRetryInterval	Cantidad de tiempo (en segundos) entre cada intento de reconexión después de identificar un error de conexión inactiva. El valor debe ser un entero entre 1 y 60.	No
loadBalanceTimeout	Tiempo mínimo (en segundos) para que la conexión resida en el grupo de conexiones antes de que se destruya la conexión.	No
commandTimeout	Tiempo de espera predeterminado (en segundos) antes de terminar el intento de ejecutar un comando y generar un error.	No
integratedSecurity	Los valores permitidos son `true` o `false`. Al especificar `false`, indique si userName y contraseña se especifican en la conexión. Al especificar `true`, indica si las credenciales actuales de la cuenta de Windows se usan para la autenticación.	No
failoverPartner	Nombre o dirección del servidor asociado al que se va a conectar si el servidor principal está inactivo.	No
maxPoolSize	Número máximo de conexiones permitidas en el grupo de conexiones para la conexión específica.	No
minPoolSize	Número mínimo de conexiones permitidas en el grupo de conexiones para la conexión específica.	No
multipleActiveResultSets	Los valores permitidos son `true` o `false`. Al especificar `true`, una aplicación puede mantener conjunto de resultados activo múltiple (MARS). Al especificar `false`, una aplicación debe procesar o cancelar todos los conjuntos de resultados de un lote para poder ejecutar cualquier otro lote en esa conexión.	No
multiSubnetFailover	Los valores permitidos son `true` o `false`. Si la aplicación se conecta a un grupo de disponibilidad AlwaysOn (AG) en subredes diferentes, establecer esta propiedad en `true` proporciona una detección y conexión más rápidas con el servidor activo actualmente.	No
packetSize	Tamaño en bytes de los paquetes de red usados para comunicarse con una instancia de servidor.	No
pooling	Los valores permitidos son `true` o `false`. Al especificar `true`, la conexión se agrupará. Al especificar `false`, la conexión se abrirá explícitamente cada vez que se solicite la conexión.	No

Autenticación SQL

Para usar la autenticación de SQL, además de las propiedades genéricas que se describen en la sección anterior, especifique las siguientes propiedades:

Propiedad	Descripción	Obligatorio
userName	Nombre de usuario que se va a usar al conectarse al servidor.	Sí
password	Contraseña del nombre de usuario. Marque este campo como SecureString para almacenarlo de forma segura. O bien puede hacer referencia a un secreto almacenado en Azure Key Vault.	No

Ejemplo: Uso de la autenticación de 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"
        }
    }
}

Ejemplo 2: Uso de la autenticación de SQL con una contraseña en 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"
        }
    }
}

Ejemplo: Use 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"
        }
    }
}

Autenticación de Windows

Para usar autenticación de Windows, además de las propiedades genéricas que se describen en la sección anterior, especifica las siguientes propiedades:

Propiedad	Descripción	Obligatorio
userName	Especifique un nombre de usuario. Un ejemplo es domainname\username.	Sí
password	Especifique la contraseña de la cuenta de usuario que se especificó para el nombre de usuario. Marque este campo como SecureString para almacenarlo de forma segura. O bien puede hacer referencia a un secreto almacenado en Azure Key Vault.	Sí

Nota:

La autenticación de Windows no es compatible en el flujo de datos.

Ejemplo: Use la autenticación de 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"
        }
    }
}

Ejemplo: Uso de la autenticación de Windows con una contraseña en 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"
        }
    }
}

Autenticación de identidad administrada asignada por el usuario

Nota:

La autenticación de identidad administrada asignada por el usuario solo se aplica a SQL Server en máquinas virtuales de Azure.

Un área de trabajo de Data Factory o de Synapse se puede asociar con una identidad administrada asignada por el usuario que represente el servicio cuando se autentica en otros recursos de Azure. Puede usar esta identidad administrada para la autenticación de SQL Server en máquinas virtuales de Azure. Puede acceder a la factoría designada o al área de trabajo de Synapse y copiar datos desde la base de datos o en la base de datos usando esta identidad.

Para usar la autenticación de identidad administrada asignada por el usuario, además de las propiedades genéricas descritas en la sección anterior, especifique las siguientes:

Propiedad	Descripción	Requerido
credentials	Especifique la identidad administrada asignada por el usuario como objeto de credencial.	Sí

También debe seguir los pasos siguientes:

Concesión de permisos a la identidad administrada asignada por el usuario.
Habilitar la autenticación de Microsoft Entra en SQL Server en máquinas virtuales de Azure.
Cree usuarios de bases de datos independientes para la identidad administrada asignada por el usuario. Conéctese a la base de datos de la que desea copiar datos (o a la que desea copiarlos) mediante alguna herramienta como SQL Server Management Studio, con una identidad de Microsoft Entra que tenga al menos permiso para MODIFICAR CUALQUIER USUARIO. Ejecute el T-SQL siguiente:
```
CREATE USER [your_resource_name] FROM EXTERNAL PROVIDER;
```
Cree una o varias identidades administradas asignadas por el usuario y conceda a la identidad administrada asignada por el usuario los permisos necesarios como haría normalmente para usuarios SQL y otros. Ejecute el código siguiente: Para más opciones, consulte este documento.
```
ALTER ROLE [role name] ADD MEMBER [your_resource_name];
```
Asigne una o varias identidades administradas asignadas por el usuario a la factoría de datos y cree credenciales para cada identidad administrada asignada por el usuario.
Configurar un servicio vinculado de SQL Server.

Ejemplo

{
    "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"
        }
    }
}

Versión heredada

Estas propiedades genéricas son compatibles con un servicio vinculado de SQL Server cuando se aplica la versión Heredada:

Propiedad	Descripción	Obligatorio
type	La propiedad type se debe establecer en: SqlServer.	Sí
alwaysEncryptedSettings	Especifique la información alwaysencryptedsettings necesaria para permitir que Always Encrypted proteja los datos confidenciales almacenados en un servidor SQL mediante una identidad administrada o una entidad de servicio. Para más información, consulte la sección Uso de Always Encrypted. Si no se especifica, la configuración predeterminada Always Encrypted está deshabilitada.	No
connectVia	Este entorno de ejecución de integración se usa para conectarse al almacén de datos. Obtenga más información en la sección Requisitos previos. Si no se especifica, se usa el valor predeterminado de Azure Integration Runtime.	No

Este conector de SQL Server admite los siguientes tipos de autenticación. Consulte las secciones correspondientes para más información.

Autenticación de SQL para la versión heredada
Autenticación de Windows para la versión heredada

Autenticación de SQL para la versión heredada

Para usar autenticación SQL, además de las propiedades genéricas que se describen en la sección anterior, especifica las siguientes propiedades:

Propiedad	Descripción	Obligatorio
connectionString	Especifique la información de connectionString necesaria para conectarse a la base de datos SQL Server. Especifique un nombre de inicio de sesión como nombre de usuario y asegúrese de que la base de datos que desea conectar está asignada a este inicio de sesión.	Sí
password	Si quiere establecer una contraseña en Azure Key Vault, extraiga la configuración de `password` de la cadena de conexión. Para obtener más información, consulte Almacenamiento de credenciales en Azure Key Vault.	No

Autenticación de Windows para la versión heredada

Para usar autenticación de Windows, además de las propiedades genéricas que se describen en la sección anterior, especifica las siguientes propiedades:

Propiedad	Descripción	Obligatorio
connectionString	Especifique la información de connectionString necesaria para conectarse a la base de datos SQL Server.	Sí
userName	Especifique un nombre de usuario. Un ejemplo es domainname\username.	Sí
password	Especifique la contraseña de la cuenta de usuario que se especificó para el nombre de usuario. Marque este campo como SecureString para almacenarlo de forma segura. O bien puede hacer referencia a un secreto almacenado en Azure Key Vault.	Sí

Propiedades del conjunto de datos

Si desea ver una lista completa de las secciones y propiedades disponibles para definir conjuntos de datos, consulte el artículo sobre conjuntos de datos. En esta sección se proporciona una lista de las propiedades que admite el conjunto de datos de SQL Server.

Las siguientes propiedades son compatibles para copiar datos con una base de datos SQL Server como origen y destino:

Propiedad	Descripción	Obligatorio
type	La propiedad type del conjunto de datos se debe establecer en SqlServerTable.	Sí
esquema	Nombre del esquema.	No para el origen, sí para el receptor
table	Nombre de la tabla o vista.	No para el origen, sí para el receptor
tableName	Nombre de la tabla o vista con el esquema. Esta propiedad permite la compatibilidad con versiones anteriores. Para la nueva carga de trabajo use `schema` y `table`.	No para el origen, sí para el receptor

Ejemplo

{
    "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>"
        }
    }
}

Propiedades de la actividad de copia

Si desea ver una lista completa de las secciones y propiedades disponibles para definir actividades, vea el artículo sobre canalizaciones. En esta sección se proporciona una lista de las propiedades que admiten el origen y receptor de SQL Server.

SQL Server como origen

Sugerencia

Para cargar datos desde SQL Server de manera eficaz mediante la creación de particiones de datos, consulte Copia en paralelo desde una base de datos SQL.

Si va a copiar datos desde SQL Server, establezca el tipo de origen de la actividad de copia en SqlSource. En la sección source de la actividad de copia se admiten las siguientes propiedades:

Propiedad	Descripción	Obligatorio
type	La propiedad type del origen de la actividad de copia debe establecerse en SqlSource.	Sí
sqlReaderQuery	Use la consulta SQL personalizada para leer los datos. Un ejemplo es `select * from MyTable`.	No
sqlReaderStoredProcedureName	Esta propiedad es el nombre del procedimiento almacenado que lee datos de la tabla de origen. La última instrucción SQL debe ser una instrucción SELECT del procedimiento almacenado.	No
storedProcedureParameters	Estos parámetros son para el procedimiento almacenado. Los valores permitidos son pares de nombre o valor. Los nombres y las mayúsculas y minúsculas de los parámetros tienen que coincidir con las mismas características de los parámetros de procedimiento almacenado.	No
isolationLevel	Especifica el comportamiento de bloqueo de transacción para el origen de SQL. Los valores permitidos son: ReadCommitted, ReadUncommitted, RepeatableRead, Serializable y Snapshot. Si no se especifica, se utiliza el nivel de aislamiento predeterminado de la base de datos. Vea este documento para obtener más detalles.	No
partitionOptions	Especifica las opciones de creación de particiones de datos que se usan para cargar datos desde SQL Server. Los valores permitidos son: None (valor predeterminado), PhysicalPartitionsOfTable y DynamicRange. Cuando se habilita una opción de partición (es decir, el valor no es `None`), el grado de paralelismo para cargar datos de manera simultánea desde SQL Server se controla mediante la opción `parallelCopies` en la actividad de copia.	No
partitionSettings	Especifique el grupo de configuración para la creación de particiones de datos. Se aplica si la opción de partición no es `None`.	No
En `partitionSettings`:
partitionColumnName	Especifique el nombre de la columna de origen de tipo entero o date/datetime (`int`, `smallint`, `bigint`, `date`, `smalldatetime`, `datetime`, `datetime2` o `datetimeoffset`) que se va a usar en la creación de particiones por rangos para la copia en paralelo. Si no se especifica, el índice o la clave primaria de la tabla se detectan automáticamente y se usan como columna de partición. Se aplica si la opción de partición es `DynamicRange`. Si usa una consulta para recuperar datos de origen, enlace `?DfDynamicRangePartitionCondition` en la cláusula WHERE. Para obtener un ejemplo, vea la sección Copia en paralelo desde una base de datos SQL.	No
partitionUpperBound	Valor máximo de la columna de partición para la división del rango de partición. Este valor se usa para decidir el intervalo de particiones, no para filtrar las filas de la tabla. Se crean particiones de todas las filas de la tabla o el resultado de la consulta y se copian. Si no se especifica, la actividad de copia detecta automáticamente el valor. Se aplica si la opción de partición es `DynamicRange`. Para obtener un ejemplo, vea la sección Copia en paralelo desde una base de datos SQL.	No
partitionLowerBound	Valor mínimo de la columna de partición para la división del rango de partición. Este valor se usa para decidir el intervalo de particiones, no para filtrar las filas de la tabla. Se crean particiones de todas las filas de la tabla o el resultado de la consulta y se copian. Si no se especifica, la actividad de copia detecta automáticamente el valor. Se aplica si la opción de partición es `DynamicRange`. Para obtener un ejemplo, vea la sección Copia en paralelo desde una base de datos SQL.	No

Tenga en cuenta los siguientes puntos:

Si se especifica sqlReaderQuery para SqlSource, la actividad de copia ejecuta la consulta en el origen de SQL Server para obtener los datos. También puede indicar un procedimiento almacenado mediante la definición de sqlReaderStoredProcedureName y storedProcedureParameters si el procedimiento almacenado adopta parámetros.
Al usar el procedimiento almacenado del origen para recuperar datos, tenga en cuenta que si está diseñado para devolver otro esquema cuando se pasa un valor de parámetro diferente, es posible que encuentre un error o vea un resultado inesperado al importar el esquema desde la interfaz de usuario, o bien al copiar datos en la base de datos SQL con la creación automática de tablas.

Ejemplo: Uso de la consulta 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>"
            }
        }
    }
]

Ejemplo: Uso de un procedimiento almacenado

"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>"
            }
        }
    }
]

Definición del procedimiento almacenado

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 como receptor

Sugerencia

Más información sobre los comportamientos de escritura, las configuraciones y los procedimientos recomendados que se admiten en Procedimiento recomendado para cargar datos en SQL Server.

Si va a copiar datos en SQL Server, establezca el tipo de receptor de la actividad de copia en SqlSink. La sección sink de la actividad de copia admite las siguientes propiedades:

Propiedad	Descripción	Obligatorio
type	La propiedad type del receptor de la actividad de copia debe establecerse en SqlSink.	Sí
preCopyScript	Esta propiedad especifica una consulta SQL para que la actividad de copia se ejecute antes de escribir datos en SQL Server. Solo se invoca una vez por cada copia que se ejecuta. Puede usar esta propiedad para limpiar los datos cargados previamente.	No
tableOption	Especifica si se crea automáticamente la tabla de receptores según el esquema de origen, si no existe. No se admite la creación automática de tablas cuando el receptor especifica un procedimiento almacenado. Los valores permitidos son: `none` (valor predeterminado), `autoCreate`.	No
sqlWriterStoredProcedureName	El nombre del procedimiento almacenado que define cómo se aplican los datos de origen en una tabla de destino. Este procedimiento almacenado se invoca por lote. Para las operaciones que solo se ejecuta una vez y que no tiene nada que ver con los datos de origen, como por ejemplo, eliminar o truncar, use la propiedad `preCopyScript`. Vea el ejemplo de Invocación del procedimiento almacenado desde el receptor de SQL.	No
storedProcedureTableTypeParameterName	Nombre del parámetro del tipo de tabla especificado en el procedimiento almacenado.	No
sqlWriterTableType	Nombre del tipo de tabla que se usará en el procedimiento almacenado. La actividad de copia dispone que los datos que se mueven estén disponibles en una tabla temporal con este tipo de tabla. El código de procedimiento almacenado puede combinar los datos copiados con datos existentes.	No
storedProcedureParameters	Parámetros del procedimiento almacenado. Los valores permitidos son pares de nombre y valor. Los nombres y las mayúsculas y minúsculas de los parámetros deben coincidir con las mismas características de los parámetros de procedimiento almacenado.	No
writeBatchSize	Número de filas que se va a insertar en la tabla SQL por lote. Los valores permitidos son enteros para el número de filas. De manera predeterminada, el servicio determina dinámicamente el tamaño adecuado del lote en función del tamaño de fila.	No
writeBatchTimeout	El tiempo de espera para que se complete la operación de inserción, upsert y el procedimiento almacenado antes de que se agote el tiempo de espera. Los valores permitidos son para el intervalo de tiempo. Un ejemplo es "00:30:00" para 30 minutos. Si no se especifica ningún valor, el valor predeterminado del tiempo de espera es "00:30:00".	No
maxConcurrentConnections	Número máximo de conexiones simultáneas establecidas en el almacén de datos durante la ejecución de la actividad. Especifique un valor solo cuando quiera limitar las conexiones simultáneas.	No
WriteBehavior	Especifique el comportamiento de escritura de la actividad de copia para cargar datos en la base de datos SQL Server. Los valores permitidos son Insert y Upsert. De forma predeterminada, el servicio usa Insert para cargar los datos.	No
Configuración de "Upsert" (actualizar/insertar)	Especifique el grupo de la configuración para el comportamiento de escritura. Se aplica cuando la opción WriteBehavior es `Upsert`.	No
En `upsertSettings`:
useTempDB	Especifica si se va a usar la tabla temporal global o la tabla física como tabla provisional para upsert. De forma predeterminada, el servicio usa la tabla temporal global como tabla provisional. El valor es `true`.	No
interimSchemaName	Especifique el esquema provisional para crear una tabla provisional si se usa la tabla física. Nota: el usuario debe tener el permiso para crear y eliminar la tabla. De forma predeterminada, la tabla provisional compartirá el mismo esquema que la tabla receptora. Se aplica cuando la opción useTempDB es `False`.	No
claves	Especifique los nombres de columna para la identificación de fila única. Se puede usar una sola clave o una serie de claves. Si no se especifica, se usa la clave principal.	No

Ejemplo 1: Anexión de datos

"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
            }
        }
    }
]

Ejemplo 2: Invocación de un procedimiento almacenado durante la copia

Para más información, vea Invocación del procedimiento almacenado desde el receptor de 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" }
                }
            }
        }
    }
]

Ejemplo 3: datos de actualizar/insertar (upsert)

"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 en paralelo desde una base de datos SQL

En la actividad de copia, el conector de SQL Server proporciona creación de particiones de datos integrada para copiar los datos en paralelo. Puede encontrar las opciones de creación de particiones de datos en la pestaña Origen de la actividad de copia.

Captura de pantalla de las opciones de partición

Al habilitar la copia con particiones, la actividad de copia ejecuta consultas en paralelo en el origen de SQL Server para cargar los datos por particiones. El grado en paralelo se controla mediante el valor parallelCopies de la actividad de copia. Por ejemplo, si establece parallelCopies en cuatro, el servicio genera y ejecuta al mismo tiempo cuatro consultas de acuerdo con la configuración y la opción de partición que ha especificado, y cada consulta recupera una porción de datos de SQL Server.

Se sugiere habilitar la copia en paralelo con la creación de particiones de datos, especialmente si se cargan grandes cantidades de datos de SQL Server. Estas son algunas configuraciones sugeridas para diferentes escenarios. Cuando se copian datos en un almacén de datos basado en archivos, se recomienda escribirlos en una carpeta como varios archivos (solo especifique el nombre de la carpeta), en cuyo caso el rendimiento es mejor que escribirlos en un único archivo.

Escenario	Configuración sugerida
Carga completa de una tabla grande con particiones físicas.	Opción de partición: particiones físicas de la tabla. Durante la ejecución, el servicio detecta automáticamente las particiones físicas y copia los datos por particiones. Para comprobar si la tabla tiene una partición física o no, puede hacer referencia a esta consulta.
Carga completa de una tabla grande, sin particiones físicas, aunque con una columna de tipo entero o datetime para la creación de particiones de datos.	Opciones de partición: partición por rangos dinámica. Columna de partición (opcional): especifique la columna usada para crear la partición de datos. Si no se especifica, se usa la columna de clave principal. Límite de partición superior y límite de partición inferior (opcional): especifique si quiere determinar el intervalo de la partición. No es para filtrar las filas de la tabla, se crean particiones de todas las filas de la tabla y se copian. Si no se especifica, la actividad de copia detecta automáticamente los valores y puede tardar mucho tiempo en función de los valores MIN y MAX. Se recomienda proporcionar límite superior e inferior. Por ejemplo, si la columna de partición "ID" tiene valores que van de 1 a 100 y establece el límite inferior en 20 y el superior en 80, con la copia en paralelo establecida en 4, el servicio recupera los datos en 4 particiones: identificadores del rango <=20, del rango [21, 50], del rango [51, 80] y del rango >=81, respectivamente.
Carga de grandes cantidades de datos mediante una consulta personalizada, sin particiones físicas, aunque con una columna de tipo entero o date/datetime para la creación de particiones de datos.	Opciones de partición: partición por rangos dinámica. Consulta: `SELECT * FROM <TableName> WHERE ?DfDynamicRangePartitionCondition AND <your_additional_where_clause>`. Columna de partición: especifique la columna usada para crear la partición de datos. Límite de partición superior y límite de partición inferior (opcional): especifique si quiere determinar el intervalo de la partición. No es para filtrar las filas de la tabla, se crean particiones de todas las filas del resultado de la consulta y se copian. Si no se especifica, la actividad de copia detecta automáticamente el valor. Por ejemplo, si la columna de partición "ID" tiene valores que van de 1 a 100 y establece el límite inferior en 20 y el superior en 80, con la copia en paralelo establecida en 4, el servicio recupera los datos en 4 particiones: identificadores del rango <=20, del rango [21, 50], del rango [51, 80] y del rango >=81, respectivamente. A continuación se muestran más consultas de ejemplo para distintos escenarios: 1. Consulta de la tabla completa: `SELECT * FROM <TableName> WHERE ?DfDynamicRangePartitionCondition` 2. Consulta de una tabla con selección de columnas y filtros adicionales de la cláusula where: `SELECT <column_list> FROM <TableName> WHERE ?DfDynamicRangePartitionCondition AND <your_additional_where_clause>` 3. Consulta con subconsultas: `SELECT <column_list> FROM (<your_sub_query>) AS T WHERE ?DfDynamicRangePartitionCondition AND <your_additional_where_clause>` 4. Consulta con partición en subconsulta: `SELECT <column_list> FROM (SELECT <your_sub_query_column_list> FROM <TableName> WHERE ?DfDynamicRangePartitionCondition) AS T`

Procedimientos recomendados para cargar datos con la opción de partición:

Seleccione una columna distintiva como columna de partición (como clave principal o clave única) para evitar la asimetría de datos.
Si la tabla tiene una partición integrada, use la opción de partición "Particiones físicas de tabla" para obtener un mejor rendimiento.
Si usa Azure Integration Runtime para copiar datos, puede establecer "unidades de integración de datos (DIU)" mayores (>4) para usar más recursos de cálculo. Compruebe los escenarios aplicables allí.
"Grado de paralelismo de copia" controla los números de partición. Si se establece en un número demasiado grande, puede resentirse el rendimiento, así que se recomienda establecerlo como (DIU o número de nodos de IR autohospedados) * (2 a 4).

Ejemplo: carga completa de una tabla grande con particiones físicas

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

Ejemplo: consulta con partición por rangos dinámica

"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>"
    }
}

Consulta de ejemplo para comprobar la partición física

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]'

Si la tabla tiene una partición física, verá "HasPartition" como "yes" como en el caso siguiente.

Resultado de la consulta SQL

Procedimiento recomendado para cargar datos en SQL Server

Al copiar datos en SQL Server, puede requerir un comportamiento de escritura diferente:

Anexión: mis datos de origen solo tienen registros nuevos.
Actualización e inserción: mis datos de origen tienen inserciones y actualizaciones.
Sobrescritura: quiero volver a cargar toda la tabla de dimensiones cada vez.
Escritura con lógica personalizada: necesito un procesamiento adicional antes de la inserción final en la tabla de destino.

Consulte las secciones correspondientes sobre cómo configurar estas operaciones y los procedimientos recomendados.

Anexión de datos

La anexión de datos es el comportamiento predeterminado de este conector de receptor de SQL Server. El servicio realiza una inserción masiva para escribir en la tabla de forma eficaz. Puede configurar el origen y el receptor según corresponda en la actividad de copia.

Actualización e inserción de datos

La actividad de copia ahora admite la carga nativa de datos en una tabla temporal de base de datos y, a continuación, actualizar los datos en la tabla receptora si existe la clave y, de lo contrario, insertar nuevos datos. Para más información sobre la configuración de upsert en las actividades de copia, consulte SQL Server como receptor.

Sobrescritura de toda la tabla

Puede configurar la propiedad preCopyScript en un receptor de la actividad de copia. En este caso, para cada actividad de copia que se ejecuta, el servicio ejecuta primero el script. Después, ejecuta la copia para insertar los datos. Por ejemplo, para sobrescribir toda la tabla con los datos más recientes, especifique un script para eliminar primero todos los registros antes de realizar la carga masiva de los nuevos datos desde el origen.

Escritura de datos con lógica personalizada

Los pasos necesarios para escribir datos con lógica personalizada son similares a los que se describen en la sección Actualización e inserción de datos. Si necesita aplicar procesamiento adicional antes de la inserción final de los datos de origen en la tabla de destino, puede cargar en una tabla de almacenamiento provisional y, luego, invocar una actividad de procedimiento almacenado, o bien invocar un procedimiento almacenado en un receptor de actividad de copia para aplicar datos.

Invocación del procedimiento almacenado desde el receptor de SQL

Al copiar datos en una base de datos de SQL Server, también se puede configurar e invocar un procedimiento almacenado especificado por el usuario con parámetros adicionales en cada lote de la tabla de origen. La característica de procedimiento almacenado aprovecha los parámetros con valores de tabla. Tenga en cuenta que el servicio encapsula automáticamente el procedimiento almacenado en su propia transacción, por lo que cualquier transacción creada dentro del procedimiento almacenado se convertirá en una transacción anidada y podría tener implicaciones para el control de excepciones.

Cuando los mecanismos de copia integrados no prestan el servicio, se puede usar un procedimiento almacenado. Por ejemplo, si quiere aplicar un procesamiento adicional antes de la inserción final de los datos de origen en la tabla de destino. Otros ejemplos de procesamiento adicional son cuando quiere combinar columnas, buscar valores adicionales e insertar datos en más de una tabla.

En el ejemplo siguiente se muestra cómo usar un procedimiento almacenado para realizar una operación UPSERT en una tabla en la base de datos SQL Server. Supongamos que los datos de entrada y la tabla Marketing del receptor tienen tres columnas: ProfileID, State y Category. Realice una operación UPSERT en función de la columna ProfileID y aplíquela solo a una categoría específica llamada "ProductA".

En la base de datos, defina el tipo de tabla con el mismo nombre que sqlWriterTableType. El esquema del tipo de tabla es el mismo que el que devuelven los datos de entrada.
```
CREATE TYPE [dbo].[MarketingType] AS TABLE(
    [ProfileID] [varchar](256) NOT NULL,
    [State] [varchar](256) NOT NULL,
    [Category] [varchar](256) NOT NULL
)
```

En la base de datos, defina el procedimiento almacenado con el mismo nombre que sqlWriterStoredProcedureName. Dicho procedimiento administra los datos de entrada del origen especificado y los combina en la tabla de salida. El nombre del parámetro del tipo de tabla del procedimiento almacenado es el mismo que el de tableName que se ha definido en el conjunto de datos.

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

Defina la sección de receptor SQL en la actividad de copia como se indica a continuación:

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

Propiedades de Asignación de instancias de Data Flow

Al transformar datos en el flujo de datos de asignación, puede leer y escribir en las tablas de la base de datos de SQL Server. Para más información, vea la transformación de origen y la transformación de receptor en los flujos de datos de asignación.

Nota

Para acceder a la instancia de SQL Server local, debe usar la red virtual administrada o el área de trabajo de Synapse de Azure Data Factory mediante un punto de conexión privado. Consulte este tutorial para ver los pasos detallados.

Transformación de origen

En la tabla siguiente se indican las propiedades que admite el origen de SQL Server. Puede editar estas propiedades en la pestaña Source options (Opciones de origen).

Nombre	Descripción	Obligatorio	Valores permitidos	Propiedad de script de flujo de datos
Tabla	Si selecciona Tabla como entrada, el flujo de datos captura todos los datos de la tabla especificada en el conjunto de datos.	No	-	-
Consultar	Si selecciona Consultar como entrada, especifique una consulta SQL para capturar datos del origen, lo que invalida cualquier tabla que especifique en el conjunto de datos. El uso de consultas es una excelente manera de reducir las filas para pruebas o búsquedas. La cláusula Ordenar por no se admite, pero puede establecer una instrucción SELECT FROM completa. También puede usar las funciones de tabla definidas por el usuario. *select from udfGetData()** es un UDF in SQL que devuelve una tabla que puede utilizar en el flujo de datos. Ejemplo de consulta: `Select * from MyTable where customerId > 1000 and customerId < 2000`	No	String	Query
Tamaño de lote	Especifique un tamaño de lote para fragmentar datos grandes en lecturas.	No	Entero	batchSize
Nivel de aislamiento	Elija uno de los siguientes niveles de aislamiento: - Read Committed - Read Uncommitted (predeterminado) - Repeatable Read - Serializable - None (ignorar el nivel de aislamiento)	No	READ_COMMITTED READ_UNCOMMITTED REPEATABLE_READ SERIALIZABLE NONE	isolationLevel
Habilitar el extracto incremental	Use esta opción para indicar a ADF que solo procese las filas que hayan cambiado desde la última vez que se ejecutó la canalización.	No	-	-
Columna de fecha incremental	Si se usa la característica de extracto incremental, debe elegir la columna de fecha y hora que desea usar como marca de agua en la tabla de origen.	No	-	-
Habilitar captura nativa de datos modificados (versión preliminar)	Use esta opción para indicar a ADF que solo procese los datos delta capturados por la tecnología de captura de datos modificados de SQL desde la última vez que se ejecutó la canalización. Con esta opción, los datos delta, incluida la inserción de filas, su actualización y su eliminación, se cargarán automáticamente sin necesidad de ninguna columna de fecha incremental. Debe habilitar la captura de datos modificados en SQL Server antes de usar esta opción en ADF. Para obtener más información sobre esta opción en ADF, consulte captura nativa de datos modificados.	No	-	-
Empezar a leer desde el principio	Si se establece esta opción con el extracto incremental, se indicará a ADF que lea todas las filas en la primera ejecución de una canalización con el extracto incremental activado.	No	-	-

Sugerencia

La expresión de tabla común (CTE) de SQL no se admite en el modo de consulta del flujo de datos de asignación, ya que el requisito previo para usar este modo es que las consultas se pueden usar en la cláusula FROM de la consulta SQL, pero las CTE no pueden hacerlo. Para usar expresiones de tabla común, debe crear un procedimiento almacenado mediante la consulta siguiente:

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

Luego, use el modo de procedimiento almacenado en la transformación de origen del flujo de datos de asignación y establezca @query como el ejemplo de with CTE as (select 'test' as a) select * from CTE. A continuación, puede usar las CTE según lo previsto.

Ejemplo de script de origen de SQL Server

Cuando se usa SQL Server como tipo de origen, el script de flujo de datos asociado es:

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

Transformación de receptor

En la tabla siguiente se indican las propiedades que admite el receptor de SQL Server. Puede editar estas propiedades en la pestaña Opciones del receptor.

Nombre	Descripción	Obligatorio	Valores permitidos	Propiedad de script de flujo de datos
Método de actualización	Especifique qué operaciones se permiten en el destino de la base de datos. El valor predeterminado es permitir solamente las inserciones. Para actualizar, upsert o eliminar filas, se requiere una transformación de alteración de fila a fin de etiquetar filas para esas acciones.	Sí	`true` o `false`	deletable insertable updateable upsertable
Columnas de clave	En el caso de las actualizaciones, upserts y eliminaciones, se deben establecer columnas de clave para determinar la fila que se va a modificar. El nombre de columna que elija como clave se usará como parte de las operaciones posteriores de actualización, upsert y eliminación. Por lo tanto, debe seleccionar una columna que exista en la asignación del receptor.	No	Array	claves
Omitir escritura de columnas de clave	Si no quiere escribir el valor en la columna de clave, seleccione "Skip writing key columns" (Omitir escritura de columnas de clave).	No	`true` o `false`	skipKeyWrites
Acción Table	determina si se deben volver a crear o quitar todas las filas de la tabla de destino antes de escribir. - Ninguno: no se realizará ninguna acción en la tabla. - Volver a crear: se quitará la tabla y se volverá a crear. Obligatorio si se crea una nueva tabla dinámicamente. - Truncar: se quitarán todas las filas de la tabla de destino.	No	`true` o `false`	recreate truncate
Tamaño de lote	Especifique el número de filas que se escriben en cada lote. Los tamaños de lote más grandes mejoran la compresión y la optimización de memoria, pero se arriesgan a obtener excepciones de memoria al almacenar datos en caché.	No	Entero	batchSize
Scripts SQL anteriores y posteriores	Especifique scripts de SQL de varias líneas que se ejecutarán antes (preprocesamiento) y después (procesamiento posterior) de que los datos se escriban en la base de datos del receptor.	No	String	preSQLs postSQLs

Sugerencia

Se recomienda dividir los scripts por lotes únicos con varios comandos en varios lotes.
Tan solo las instrucciones de lenguaje de definición de datos (DDL) y lenguaje de manipulación de datos (DML) que devuelven un recuento de actualizaciones sencillo se pueden ejecutar como parte de un lote. Obtenga más información en Realización de operaciones por lotes

Ejemplo de script de receptor de SQL Server

Cuando se usa SQL Server como tipo de receptor, el script de flujo de datos asociado es:

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

Asignación de tipos de datos para SQL Server

Cuando copia datos con SQL Server como origen y destino, se usan las siguientes asignaciones de tipos de datos de SQL Server para los tipos de datos provisionales de Azure Data Factory. Las canalizaciones de Synapse, que implementan Data Factory, usan las mismas asignaciones. Consulte el artículo sobre asignaciones de tipos de datos y esquema para más información sobre cómo la actividad de copia asigna el tipo de datos y el esquema de origen al receptor.

Tipos de datos de SQL Server	Tipo de datos provisionales de Data Factory
bigint	Int64
binary	Byte[]
bit	Boolean
char	String, Char[]
date	DateTime
Datetime	DateTime
datetime2	DateTime
Datetimeoffset	DateTimeOffset
Decimal	Decimal
FILESTREAM attribute (varbinary(max))	Byte[]
Float	Double
imagen	Byte[]
int	Int32
money	Decimal
NCHAR	String, Char[]
ntext	String, Char[]
NUMERIC	Decimal
NVARCHAR	String, Char[]
real	Single
rowversion	Byte[]
smalldatetime	DateTime
SMALLINT	Int16
SMALLMONEY	Decimal
sql_variant	Object
text	String, Char[]
time	TimeSpan
timestamp	Byte[]
TINYINT	Int16
UNIQUEIDENTIFIER	Guid
varbinary	Byte[]
varchar	String, Char[]
Xml	String

Nota:

En el caso de los tipos de datos que se asignan al tipo decimal provisional, la actividad de copia actualmente admite una precisión de hasta 28. Si tiene datos que requieren una precisión mayor que 28, considere la posibilidad de convertir a una cadena en una consulta SQL.

Al copiar datos de SQL Server mediante Azure Data Factory, el tipo de datos de bits se asigna al tipo de datos provisional booleano. Si tiene datos que deben conservarse como el tipo de datos de bits, use consultas con T-SQL CAST o CONVERT.

Propiedades de la actividad de búsqueda

Para obtener información detallada sobre las propiedades, consulte Actividad de búsqueda.

Propiedades de la actividad GetMetadata

Para información detallada sobre las propiedades, consulte Actividad de obtención de metadatos.

Uso de Always Encrypted

Al copiar datos desde o hacia SQL Server con Always Encrypted, siga estos pasos:

Almacene la clave maestra de columna (CMK) en una instancia de Azure Key Vault. Obtenga más información acerca de la configuración de Always Encrypted con Azure Key Vault
Asegúrese de conceder acceso al almacén de claves donde se almacena la clave maestra de columna (CMK). Consulte este artículo para obtener información acerca de los permisos necesarios.
Cree un servicio vinculado para conectarse a la base de datos SQL y habilitar la función "Always Encrypted" mediante una identidad administrada o una entidad de servicio.

Nota

La función Always Encrypted de SQL Server admite los siguientes escenarios:

Los almacenes de datos de origen o receptores usan la identidad administrada o la entidad de servicio como tipo de autenticación del proveedor de claves.
Los almacenes de datos de origen y receptores usan la identidad administrada como tipo de autenticación del proveedor de claves.
Los almacenes de datos de origen y receptores usan la misma entidad de servicio que el tipo de autenticación del proveedor de claves.

Nota

Actualmente, Always Encrypted de SQL Server solo se admite para la transformación de origen en los flujos de datos de asignación.

Captura nativa de datos modificados

Azure Data Factory puede admitir capacidades de captura nativa de datos modificados para SQL Server, Azure SQL DB y Azure SQL MI. Los datos modificados, incluida la inserción, actualización y eliminación de filas en almacenes SQL, se pueden detectar y extraer automáticamente mediante el flujo de datos de asignación de ADF. Con la experiencia sin código en el flujo de datos de asignación, los usuarios pueden alcanzar fácilmente el escenario de replicación de datos desde almacenes SQL, anexando una base de datos como almacén de destino. Es más, los usuarios también pueden componer cualquier lógica de transformación de datos entre medias para lograr un escenario de ETL incremental desde almacenes SQL.

No debe cambiar el nombre de la canalización ni de la actividad para que ADF pueda registrar el punto de control y usted pueda obtener automáticamente los datos modificados desde la última ejecución. Si cambia el nombre de la canalización o de la actividad, el punto de control se restablecerá, por lo que tendría que empezar desde el principio u obtener los cambios que se realicen a partir de ese momento en la siguiente ejecución. Si desea cambiar el nombre de la canalización o el nombre de la actividad, pero mantener el punto de control para obtener automáticamente los datos modificados de la última ejecución, use su propia clave de punto de control en la actividad del flujo de datos.

Cuando se depura la canalización, esta característica funciona igual. Tenga en cuenta que el punto de control se restablecerá cuando se actualice el explorador durante la ejecución de depuración. Cuando esté conforme con el resultado de la canalización obtenida a partir de la ejecución de depuración, podrá publicar y desencadenar la canalización. En el momento en que desencadene por primera vez la canalización publicada, la canalización se reiniciará automáticamente desde el principio o se obtendrán los cambios a partir de ese momento.

Siempre puede volver a ejecutar la canalización en la sección de supervisión. Si lo hace, siempre se capturarán los datos modificados a partir del punto de control anterior de la ejecución de la canalización seleccionada.

Ejemplo 1:

Cuando encadene directamente una transformación de origen referenciada al conjunto de datos habilitado para CDC de SQL con una transformación de receptor referenciada a una base de datos en un flujo de datos de asignación, los cambios que se producen en el origen de SQL se aplicarán automáticamente a la base de datos de destino, de modo que obtendrá fácilmente el escenario de replicación de datos entre bases de datos. Puede usar el método de actualización en la transformación del receptor para seleccionar si desea permitir la inserción, la actualización o la eliminación en la base de datos de destino. El script de ejemplo en el flujo de datos de asignación es el siguiente.

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

Ejemplo 2:

Si quiere habilitar el escenario ETL en vez de la replicación de datos entre la base de datos a través de CDC de SQL, puede usar expresiones en el flujo de datos de asignación, incluidas isInsert(1), isUpdate(1) y isDelete(1) para diferenciar las filas con distintos tipos de operación. A continuación se muestra uno de los scripts de ejemplo para asignar flujos de datos en la derivación de una columna con el valor: 1 para indicar filas insertadas, 2 para indicar filas actualizadas y 3 para indicar filas eliminadas en las transformaciones de bajada para procesar los datos 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

Limitación conocida:

ADF cargará solo los cambios netos de CDC de SQL a través de cdc.fn_cdc_get_net_changes_.

Solución de problemas de conexión

Configure su instancia de SQL Server para que acepte conexiones remotas. Inicie SQL Server Management Studio, haga clic con el botón derecho en servidor y seleccione Propiedades. Seleccione Conexiones en la lista y marque la casilla Permitir conexiones remotas con este servidor.

Consulte los pasos detallados en el artículo Configurar la opción de configuración del servidor Acceso remoto.
Inicie el Administrador de configuración de SQL Server. Expanda Configuración de red de SQL Server para la instancia que desee y seleccione Protocols for MSSQLSERVER (Protocolos para MSSQLSERVER). Los protocolos aparecen en el panel derecho. Para habilitar TCP/IP, haga clic con el botón derecho en TCP/IP y seleccione Habilitar.

Para obtener más información y maneras alternativas de habilitar el protocolo TCP/IP, consulte Habilitar o deshabilitar un protocolo de red de servidor.
En la misma ventana, haga doble clic en TCP/IP para abrir la ventana TCP/IP Properties (Propiedades de TCP/IP).
Cambie a la pestaña Direcciones IP . Desplácese hacia abajo hasta la sección IPAll. Escriba el puerto TCP. El valor predeterminado es 1433.
Cree una regla del Firewall de Windows en la máquina para permitir el tráfico entrante a través de este puerto.
Verifique la conexión: use SQL Server Management Studio en una máquina diferente para conectarse a SQL Server con un nombre completo. Un ejemplo es "<machine>.<domain>.corp.<company>.com,1433".

Actualización de la versión de SQL Server

Para actualizar a la versión de SQL Server, en la página Editar servicio vinculado, selecciona Recomendado en Versión y configura el servicio vinculado haciendo referencia a las propiedades del servicio vinculado para la versión recomendada.

Diferencias entre la versión recomendada y la heredada

En la siguiente tabla, se muestran las diferencias entre el uso de la versión recomendada y la heredada por parte de SQL Server.

Versión recomendada	Versión heredada
Admite TLS 1.3 a través de `encrypt` como `strict`.	No se admite TLS 1.3.

Para obtener una lista de almacenes de datos que la actividad de copia admite como orígenes y receptores, vea Almacenes de datos que se admiten.

Comparteix a través de

Copia y transformación de datos en SQL Server mediante Azure Data Factory o Azure Synapse Analytics

Funcionalidades admitidas

Requisitos previos

Introducción

Creación de un servicio vinculado de SQL Server mediante la interfaz de usuario

Detalles de configuración del conector

Propiedades del servicio vinculado

Versión recomendada

Autenticación SQL

Autenticación de Windows

Autenticación de identidad administrada asignada por el usuario

Versión heredada

Autenticación de SQL para la versión heredada

Autenticación de Windows para la versión heredada

Propiedades del conjunto de datos

Propiedades de la actividad de copia

SQL Server como origen

SQL Server como receptor

Copia en paralelo desde una base de datos SQL

Consulta de ejemplo para comprobar la partición física

Procedimiento recomendado para cargar datos en SQL Server

Anexión de datos

Actualización e inserción de datos

Sobrescritura de toda la tabla

Escritura de datos con lógica personalizada

Invocación del procedimiento almacenado desde el receptor de SQL

Propiedades de Asignación de instancias de Data Flow

Transformación de origen

Ejemplo de script de origen de SQL Server

Transformación de receptor

Ejemplo de script de receptor de SQL Server

Asignación de tipos de datos para SQL Server

Propiedades de la actividad de búsqueda

Propiedades de la actividad GetMetadata

Uso de Always Encrypted

Captura nativa de datos modificados

Ejemplo 1:

Ejemplo 2:

Limitación conocida:

Solución de problemas de conexión

Actualización de la versión de SQL Server

Diferencias entre la versión recomendada y la heredada

Contenido relacionado

Comentaris

Recursos addicionals