Gegevens verplaatsen naar en van SQL Server met behulp van Azure Data Factory

Notitie

Dit artikel is van toepassing op versie 1 van Data Factory. Als u de huidige versie van de Data Factory-service gebruikt, raadpleegt u SQL Server connector in V2.

In dit artikel wordt uitgelegd hoe u de kopieeractiviteit in Azure Data Factory kunt gebruiken om gegevens naar/van een SQL Server-database te verplaatsen. Het is gebaseerd op het artikel Activiteiten voor gegevensverplaatsing , waarin een algemeen overzicht wordt weergegeven van gegevensverplaatsing met de kopieeractiviteit.

Notitie

Voor interactie met Azure wordt de Azure Az PowerShell-module aanbevolen. Zie Azure PowerShell installeren om aan de slag te gaan. Raadpleeg Azure PowerShell migreren van AzureRM naar Az om te leren hoe u naar de Azure PowerShell-module migreert.

Ondersteunde scenario's

U kunt gegevens uit een SQL Server-database kopiëren naar de volgende gegevensarchieven:

Categorie Gegevensarchief
Azure Azure Blob Storage
Azure Data Lake Storage Gen1
Azure Cosmos DB (SQL API)
Azure SQL Database
Azure Synapse Analytics
Azure Cognitive Search-index
Azure Table storage
Databases SQL Server
Oracle
File Bestandssysteem

U kunt gegevens uit de volgende gegevensarchieven kopiëren naar een SQL Server-database:

Categorie Gegevensarchief
Azure Azure Blob Storage
Azure Cosmos DB (SQL API)
Azure Data Lake Storage Gen1
Azure SQL Database
Azure Synapse Analytics
Azure Table storage
Databases Amazon Redshift
DB2
MySQL
Oracle
PostgreSQL
SAP Business Warehouse
SAP HANA
SQL Server
Sybase
Teradata
NoSQL Cassandra
MongoDB
Bestand Amazon S3
Bestandssysteem
FTP
HDFS
SFTP
Andere Algemene HTTP
Algemene OData
Algemene ODBC
Salesforce
Webtabel (tabel van HTML)

Ondersteunde SQL Server-versies

Deze SQL Server-connector biedt ondersteuning voor het kopiëren van gegevens van/naar de volgende versies van een on-premises exemplaar of in Azure IaaS met zowel SQL-verificatie als Windows-verificatie: SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008 SQL Server 2005

Connectiviteit inschakelen

De concepten en stappen die nodig zijn om verbinding te maken met SQL Server on-premises of in Azure IaaS-VM's (Infrastructure-as-a-Service) zijn hetzelfde. In beide gevallen moet u Gegevensbeheer Gateway gebruiken voor connectiviteit.

Zie het artikel over het verplaatsen van gegevens tussen on-premises locaties en cloud voor meer informatie over Gegevensbeheer Gateway en stapsgewijze instructies voor het instellen van de gateway. Het instellen van een gateway-exemplaar is een vereiste voor het maken van verbinding met SQL Server.

Hoewel u de gateway op dezelfde on-premises machine of cloud-VM-exemplaar kunt installeren als de SQL Server voor betere prestaties, raden we u aan deze op afzonderlijke computers te installeren. Als u de gateway en SQL Server op afzonderlijke computers hebt, vermindert u de conflicten met resources.

Aan de slag

U kunt een pijplijn maken met een kopieeractiviteit waarmee gegevens worden verplaatst naar/van een SQL Server-database met behulp van verschillende hulpprogramma's/API's.

De eenvoudigste manier om een pijplijn te maken, is door de wizard Kopiëren te gebruiken. Zie zelfstudie: Een pijplijn maken met de wizard Kopiëren voor een beknopt overzicht van het maken van een pijplijn met behulp van de wizard Gegevens kopiëren.

U kunt ook de volgende hulpprogramma's gebruiken om een pijplijn te maken: Visual Studio, Azure PowerShell, Azure Resource Manager-sjabloon, .NET API en REST API. Zie Copy-activiteit zelfstudie voor stapsgewijze instructies voor het maken van een pijplijn met een kopieeractiviteit.

Ongeacht of u de hulpprogramma's of API's gebruikt, voert u de volgende stappen uit om een pijplijn te maken waarmee gegevens uit een brongegevensarchief worden verplaatst naar een sinkgegevensarchief:

  1. Maak een data factory. Een data factory kan een of meer pijplijnen bevatten.
  2. Maak gekoppelde services om invoer- en uitvoergegevensarchieven te koppelen aan uw data factory. Als u bijvoorbeeld gegevens kopieert van een SQL Server-database naar een Azure-blobopslag, maakt u twee gekoppelde services om uw SQL Server-database en Azure-opslagaccount te koppelen aan uw data factory. Zie de sectie Gekoppelde service-eigenschappen die specifiek zijn voor SQL Server database.
  3. Maak gegevenssets om invoer- en uitvoergegevens voor de kopieerbewerking weer te geven. In het voorbeeld dat in de laatste stap wordt vermeld, maakt u een gegevensset om de SQL-tabel op te geven in uw SQL Server-database die de invoergegevens bevat. En u maakt een andere gegevensset om de blobcontainer en de map op te geven die de gegevens bevat die zijn gekopieerd uit de SQL Server-database. Zie de sectie Eigenschappen van gegevenssets die specifiek zijn voor SQL Server database.
  4. Maak een pijplijn met een kopieeractiviteit die een gegevensset als invoer en een gegevensset als uitvoer gebruikt. In het eerder genoemde voorbeeld gebruikt u SqlSource als bron en BlobSink als sink voor de kopieeractiviteit. Als u ook kopieert van Azure Blob Storage naar SQL Server Database, gebruikt u BlobSource en SqlSink in de kopieeractiviteit. Zie de sectie Eigenschappen van kopieeractiviteit die specifiek zijn voor SQL Server Database. Klik op de koppeling in de vorige sectie voor uw gegevensarchief voor meer informatie over het gebruik van een gegevensarchief als bron of sink.

Wanneer u de wizard gebruikt, worden JSON-definities voor deze Data Factory-entiteiten (gekoppelde services, gegevenssets en de pijplijn) automatisch voor u gemaakt. Wanneer u hulpprogramma's/API's (behalve .NET API) gebruikt, definieert u deze Data Factory-entiteiten met behulp van de JSON-indeling. Zie de sectie JSON-voorbeelden van dit artikel voor voorbeelden met JSON-definities voor Data Factory-entiteiten die worden gebruikt voor het kopiëren van gegevens naar/van een SQL Server-database.

De volgende secties bevatten informatie over JSON-eigenschappen die worden gebruikt om Data Factory-entiteiten te definiëren die specifiek zijn voor SQL Server:

Gekoppelde service-eigenschappen

U maakt een gekoppelde service van het type OnPremisesSqlServer om een SQL Server-database te koppelen aan een data factory. De volgende tabel bevat een beschrijving voor JSON-elementen die specifiek zijn voor SQL Server gekoppelde service.

De volgende tabel bevat een beschrijving voor JSON-elementen die specifiek zijn voor SQL Server gekoppelde service.

Eigenschap Beschrijving Vereist
type De typeeigenschap moet worden ingesteld op: OnPremisesSqlServer. Ja
connectionString Geef connectionString-informatie op die nodig is om verbinding te maken met de SQL Server-database met behulp van SQL-verificatie of Windows-verificatie. Yes
gatewayName De naam van de gateway die de Data Factory-service moet gebruiken om verbinding te maken met de SQL Server-database. Yes
gebruikersnaam Geef de gebruikersnaam op als u Windows-verificatie gebruikt. Voorbeeld: domeinnaam\gebruikersnaam. No
wachtwoord Geef het wachtwoord op voor het gebruikersaccount dat u hebt opgegeven voor de gebruikersnaam. No

U kunt referenties versleutelen met de cmdlet New-AzDataFactoryEncryptValue en deze gebruiken in de connection string zoals wordt weergegeven in het volgende voorbeeld (EncryptedCredential-eigenschap):

"connectionString": "Data Source=<servername>;Initial Catalog=<databasename>;Integrated Security=True;EncryptedCredential=<encrypted credential>",

Voorbeelden

JSON voor het gebruik van SQL-verificatie

{
    "name": "MyOnPremisesSQLDB",
    "properties":
    {
        "type": "OnPremisesSqlServer",
        "typeProperties": {
            "connectionString": "Data Source=<servername>;Initial Catalog=MarketingCampaigns;Integrated Security=False;User ID=<username>;Password=<password>;",
            "gatewayName": "<gateway name>"
        }
    }
}

JSON voor het gebruik van Windows-verificatie

Gegevensbeheer Gateway imiteert het opgegeven gebruikersaccount om verbinding te maken met de SQL Server-database.

{
    "Name": " MyOnPremisesSQLDB",
    "Properties":
    {
        "type": "OnPremisesSqlServer",
        "typeProperties": {
            "ConnectionString": "Data Source=<servername>;Initial Catalog=MarketingCampaigns;Integrated Security=True;",
            "username": "<domain\\username>",
            "password": "<password>",
            "gatewayName": "<gateway name>"
        }
    }
}

Eigenschappen van gegevensset

In de voorbeelden hebt u een gegevensset van het type SqlServerTable gebruikt om een tabel in een SQL Server-database weer te geven.

Zie het artikel Gegevenssets maken voor een volledige lijst met sectie-eigenschappen & die beschikbaar zijn voor het definiëren van gegevenssets. Secties zoals structuur, beschikbaarheid en beleid van een JSON-gegevensset zijn vergelijkbaar voor alle typen gegevenssets (SQL Server, Azure-blob, Azure-tabel, enzovoort).

De sectie typeProperties verschilt voor elk type gegevensset en biedt informatie over de locatie van de gegevens in het gegevensarchief. De sectie typeProperties voor de gegevensset van het type SqlServerTable heeft de volgende eigenschappen:

Eigenschap Beschrijving Vereist
tableName De naam van de tabel of weergave in het SQL Server Database-exemplaar waarnaar de gekoppelde service verwijst. Yes

Eigenschappen van de kopieeractiviteit

Als u gegevens verplaatst van een SQL Server-database, stelt u het brontype in de kopieeractiviteit in op SqlSource. Als u gegevens naar een SQL Server-database verplaatst, stelt u het sinktype in de kopieeractiviteit in op SqlSink. Deze sectie bevat een lijst met eigenschappen die worden ondersteund door SqlSource en SqlSink.

Zie het artikel Pijplijnen maken voor een volledige lijst met sectie-eigenschappen & die beschikbaar zijn voor het definiëren van activiteiten. Eigenschappen zoals naam, beschrijving, invoer- en uitvoertabellen en beleidsregels zijn beschikbaar voor alle typen activiteiten.

Notitie

De kopieeractiviteit heeft slechts één invoer en produceert slechts één uitvoer.

Terwijl de eigenschappen die beschikbaar zijn in de sectie typeProperties van de activiteit, variëren met elk activiteitstype. Voor Copy-activiteit variëren ze afhankelijk van de typen bronnen en sinks.

SqlSource

Wanneer de bron in een kopieeractiviteit van het type SqlSource is, zijn de volgende eigenschappen beschikbaar in de sectie typeProperties :

Eigenschap Beschrijving Toegestane waarden Vereist
sqlReaderQuery Gebruik de aangepaste query om gegevens te lezen. SQL-queryreeks. Bijvoorbeeld: selecteer * in Mijn tabel. Kan verwijzen naar meerdere tabellen uit de database waarnaar wordt verwezen door de invoergegevensset. Als dit niet is opgegeven, selecteert u de SQL-instructie die wordt uitgevoerd in MyTable. No
sqlReaderStoredProcedureName Naam van de opgeslagen procedure waarmee gegevens uit de brontabel worden gelezen. Naam van de opgeslagen procedure. De laatste SQL-instructie moet een SELECT-instructie zijn in de opgeslagen procedure. No
storedProcedureParameters Parameters voor de opgeslagen procedure. Naam-/waardeparen. Namen en hoofdletters van parameters moeten overeenkomen met de namen en behuizing van de opgeslagen procedureparameters. No

Als de sqlReaderQuery is opgegeven voor sqlSource, voert de kopieeractiviteit deze query uit op de SQL Server databasebron om de gegevens op te halen.

U kunt ook een opgeslagen procedure opgeven door de sqlReaderStoredProcedureName en storedProcedureParameters op te geven (als de opgeslagen procedure parameters gebruikt).

Als u sqlReaderQuery of sqlReaderStoredProcedureName niet opgeeft, worden de kolommen die in de structuursectie zijn gedefinieerd, gebruikt om een selectiequery te maken die moet worden uitgevoerd op de SQL Server Database. Als de definitie van de gegevensset niet de structuur heeft, worden alle kolommen uit de tabel geselecteerd.

Notitie

Wanneer u sqlReaderStoredProcedureName gebruikt, moet u nog steeds een waarde opgeven voor de eigenschap tableName in de JSON-gegevensset. Er zijn echter geen validaties uitgevoerd voor deze tabel.

SqlSink

SqlSink ondersteunt de volgende eigenschappen:

Eigenschap Beschrijving Toegestane waarden Vereist
writeBatchTimeout Wachttijd voordat de batch-invoegbewerking is voltooid voordat er een time-out optreedt. tijdsbestek

Voorbeeld: '00:30:00' (30 minuten).
No
writeBatchSize Hiermee voegt u gegevens in de SQL-tabel in wanneer de buffergrootte writeBatchSize bereikt. Geheel getal (aantal rijen) Nee (standaard: 10000)
sqlWriterCleanupScript Geef een query op voor kopieeractiviteit die moet worden uitgevoerd, zodat de gegevens van een specifiek segment worden opgeschoond. Zie de sectie Herhaalbare kopie voor meer informatie. Een query-instructie. No
sliceIdentifierColumnName Geef de kolomnaam op voor kopieeractiviteit die moet worden gevuld met de automatisch gegenereerde slice-id, die wordt gebruikt om gegevens van een specifiek segment op te schonen wanneer deze opnieuw worden uitgevoerd. Zie de sectie Herhaalbare kopie voor meer informatie. Kolomnaam van een kolom met gegevenstype binair(32). No
sqlWriterStoredProcedureName Naam van de opgeslagen procedure die definieert hoe brongegevens in de doeltabel moeten worden toegepast, bijvoorbeeld om upserts uit te voeren of te transformeren met behulp van uw eigen bedrijfslogica.

Houd er rekening mee dat deze opgeslagen procedure per batch wordt aangeroepen. Als u bewerkingen wilt uitvoeren die slechts eenmaal worden uitgevoerd en niets te maken heeft met brongegevens, bijvoorbeeld verwijderen/afkappen, gebruikt u sqlWriterCleanupScript de eigenschap.
Naam van de opgeslagen procedure. No
storedProcedureParameters Parameters voor de opgeslagen procedure. Naam-/waardeparen. Namen en hoofdletters van parameters moeten overeenkomen met de namen en behuizing van de opgeslagen procedureparameters. No
sqlWriterTableType Geef de naam van het tabeltype op die moet worden gebruikt in de opgeslagen procedure. Copy-activiteit maakt de gegevens beschikbaar in een tijdelijke tabel met dit tabeltype. Opgeslagen procedurecode kan vervolgens de gegevens samenvoegen die worden gekopieerd met bestaande gegevens. Een tabeltypenaam. No

JSON-voorbeelden voor het kopiëren van en naar SQL Server

De volgende voorbeelden bevatten voorbeeld-JSON-definities die u kunt gebruiken om een pijplijn te maken met behulp van Visual Studio of Azure PowerShell. In de volgende voorbeelden ziet u hoe u gegevens kopieert naar en van SQL Server en Azure Blob Storage. Gegevens kunnen echter rechtstreeks vanuit een van de bronnen naar een van de sinks worden gekopieerd die hier worden vermeld met behulp van de kopieeractiviteit in Azure Data Factory.

Voorbeeld: Gegevens kopiëren van SQL Server naar Azure Blob

In het volgende voorbeeld ziet u:

  1. Een gekoppelde service van het type OnPremisesSqlServer.
  2. Een gekoppelde service van het type AzureStorage.
  3. Een invoergegevensset van het type SqlServerTable.
  4. Een uitvoergegevensset van het type AzureBlob.
  5. De pijplijn met Copy-activiteit die gebruikmaakt van SqlSource en BlobSink.

In het voorbeeld worden tijdreeksgegevens van een SQL Server-tabel elk uur gekopieerd naar een Azure-blob. De JSON-eigenschappen die in deze voorbeelden worden gebruikt, worden beschreven in secties na de voorbeelden.

Als eerste stap moet u de gegevensbeheergateway instellen. De instructies bevinden zich in het verplaatsen van gegevens tussen on-premises locaties en het artikel over de cloud .

gekoppelde service SQL Server

{
  "Name": "SqlServerLinkedService",
  "properties": {
    "type": "OnPremisesSqlServer",
    "typeProperties": {
      "connectionString": "Data Source=<servername>;Initial Catalog=<databasename>;Integrated Security=False;User ID=<username>;Password=<password>;",
      "gatewayName": "<gatewayname>"
    }
  }
}

Gekoppelde Azure Blob Storage-service

{
  "name": "StorageLinkedService",
  "properties": {
    "type": "AzureStorage",
    "typeProperties": {
      "connectionString": "DefaultEndpointsProtocol=https;AccountName=<accountname>;AccountKey=<accountkey>"
    }
  }
}

SQL Server invoergegevensset

In het voorbeeld wordt ervan uitgegaan dat u een tabel 'MyTable' hebt gemaakt in SQL Server en dat deze een kolom bevat met de naam 'timestampcolumn' voor tijdreeksgegevens. U kunt query's uitvoeren op meerdere tabellen in dezelfde database met behulp van één gegevensset, maar één tabel moet worden gebruikt voor het typeProperty van de tabelnaam van de gegevensset.

Het instellen van 'extern': 'true' informeert Data Factory-service dat de gegevensset extern is van de data factory en niet wordt geproduceerd door een activiteit in de data factory.

{
  "name": "SqlServerInput",
  "properties": {
    "type": "SqlServerTable",
    "linkedServiceName": "SqlServerLinkedService",
    "typeProperties": {
      "tableName": "MyTable"
    },
    "external": true,
    "availability": {
      "frequency": "Hour",
      "interval": 1
    },
    "policy": {
      "externalData": {
        "retryInterval": "00:01:00",
        "retryTimeout": "00:10:00",
        "maximumRetry": 3
      }
    }
  }
}

Azure Blob-uitvoergegevensset

Gegevens worden elk uur naar een nieuwe blob geschreven (frequentie: uur, interval: 1). Het mappad voor de blob wordt dynamisch geëvalueerd op basis van de begintijd van het segment dat wordt verwerkt. Het mappad maakt gebruik van delen jaar, maand, dag en uren van de begintijd.

{
  "name": "AzureBlobOutput",
  "properties": {
    "type": "AzureBlob",
    "linkedServiceName": "StorageLinkedService",
    "typeProperties": {
      "folderPath": "mycontainer/myfolder/yearno={Year}/monthno={Month}/dayno={Day}/hourno={Hour}",
      "partitionedBy": [
        {
          "name": "Year",
          "value": {
            "type": "DateTime",
            "date": "SliceStart",
            "format": "yyyy"
          }
        },
        {
          "name": "Month",
          "value": {
            "type": "DateTime",
            "date": "SliceStart",
            "format": "MM"
          }
        },
        {
          "name": "Day",
          "value": {
            "type": "DateTime",
            "date": "SliceStart",
            "format": "dd"
          }
        },
        {
          "name": "Hour",
          "value": {
            "type": "DateTime",
            "date": "SliceStart",
            "format": "HH"
          }
        }
      ],
      "format": {
        "type": "TextFormat",
        "columnDelimiter": "\t",
        "rowDelimiter": "\n"
      }
    },
    "availability": {
      "frequency": "Hour",
      "interval": 1
    }
  }
}

Pijplijn met Copy-activiteit

De pijplijn bevat een kopieeractiviteit die is geconfigureerd voor het gebruik van deze invoer- en uitvoergegevenssets en die elk uur moet worden uitgevoerd. In de JSON-definitie van de pijplijn wordt het brontype ingesteld op SqlSource en is het sinktype ingesteld op BlobSink. De SQL-query die is opgegeven voor de eigenschap SqlReaderQuery , selecteert de gegevens in het afgelopen uur om te kopiëren.

{
  "name":"SamplePipeline",
  "properties":{
    "start":"2016-06-01T18:00:00",
    "end":"2016-06-01T19:00:00",
    "description":"pipeline for copy activity",
    "activities":[
      {
        "name": "SqlServertoBlob",
        "description": "copy activity",
        "type": "Copy",
        "inputs": [
          {
            "name": " SqlServerInput"
          }
        ],
        "outputs": [
          {
            "name": "AzureBlobOutput"
          }
        ],
        "typeProperties": {
          "source": {
            "type": "SqlSource",
            "SqlReaderQuery": "$$Text.Format('select * from MyTable where timestampcolumn >= \\'{0:yyyy-MM-dd HH:mm}\\' AND timestampcolumn < \\'{1:yyyy-MM-dd HH:mm}\\'', WindowStart, WindowEnd)"
          },
          "sink": {
            "type": "BlobSink"
          }
        },
        "scheduler": {
          "frequency": "Hour",
          "interval": 1
        },
        "policy": {
          "concurrency": 1,
          "executionPriorityOrder": "OldestFirst",
          "retry": 0,
          "timeout": "01:00:00"
        }
      }
    ]
  }
}

In dit voorbeeld wordt sqlReaderQuery opgegeven voor sqlSource. Met de kopieeractiviteit wordt deze query uitgevoerd op de SQL Server databasebron om de gegevens op te halen. U kunt ook een opgeslagen procedure opgeven door de sqlReaderStoredProcedureName en storedProcedureParameters op te geven (als de opgeslagen procedure parameters gebruikt). De sqlReaderQuery kan verwijzen naar meerdere tabellen in de database waarnaar wordt verwezen door de invoergegevensset. Het is niet beperkt tot alleen de tabelset als typeProperty van de gegevensset.

Als u sqlReaderQuery of sqlReaderStoredProcedureName niet opgeeft, worden de kolommen die in de structuursectie zijn gedefinieerd, gebruikt om een selectiequery te maken die moet worden uitgevoerd op de SQL Server Database. Als de definitie van de gegevensset niet de structuur heeft, worden alle kolommen uit de tabel geselecteerd.

Zie de sectie Sql-bron en BlobSink voor de lijst met eigenschappen die worden ondersteund door SqlSource en BlobSink.

Voorbeeld: Gegevens kopiëren van Azure Blob naar SQL Server

In het volgende voorbeeld ziet u:

  1. De gekoppelde service van het type OnPremisesSqlServer.
  2. De gekoppelde service van het type AzureStorage.
  3. Een invoergegevensset van het type AzureBlob.
  4. Een uitvoergegevensset van het type SqlServerTable.
  5. De pijplijn met Copy-activiteit die gebruikmaakt van BlobSource en SqlSink.

In het voorbeeld worden tijdreeksgegevens van een Azure-blob elk uur gekopieerd naar een SQL Server tabel. De JSON-eigenschappen die in deze voorbeelden worden gebruikt, worden beschreven in secties na de voorbeelden.

gekoppelde service SQL Server

{
  "Name": "SqlServerLinkedService",
  "properties": {
    "type": "OnPremisesSqlServer",
    "typeProperties": {
      "connectionString": "Data Source=<servername>;Initial Catalog=<databasename>;Integrated Security=False;User ID=<username>;Password=<password>;",
      "gatewayName": "<gatewayname>"
    }
  }
}

Gekoppelde Azure Blob Storage-service

{
  "name": "StorageLinkedService",
  "properties": {
    "type": "AzureStorage",
    "typeProperties": {
      "connectionString": "DefaultEndpointsProtocol=https;AccountName=<accountname>;AccountKey=<accountkey>"
    }
  }
}

Azure Blob-invoergegevensset

Gegevens worden elk uur opgehaald uit een nieuwe blob (frequentie: uur, interval: 1). Het mappad en de bestandsnaam voor de blob worden dynamisch geëvalueerd op basis van de begintijd van het segment dat wordt verwerkt. Het pad naar de map maakt gebruik van het gedeelte jaar, maand en dag van de begintijd en bestandsnaam gebruikt het uurgedeelte van de begintijd. 'extern': 'true'-instelling informeert de Data Factory-service dat de gegevensset zich buiten de data factory bevindt en niet wordt geproduceerd door een activiteit in de data factory.

{
  "name": "AzureBlobInput",
  "properties": {
    "type": "AzureBlob",
    "linkedServiceName": "StorageLinkedService",
    "typeProperties": {
      "folderPath": "mycontainer/myfolder/yearno={Year}/monthno={Month}/dayno={Day}",
      "fileName": "{Hour}.csv",
      "partitionedBy": [
        {
          "name": "Year",
          "value": {
            "type": "DateTime",
            "date": "SliceStart",
            "format": "yyyy"
          }
        },
        {
          "name": "Month",
          "value": {
            "type": "DateTime",
            "date": "SliceStart",
            "format": "MM"
          }
        },
        {
          "name": "Day",
          "value": {
            "type": "DateTime",
            "date": "SliceStart",
            "format": "dd"
          }
        },
        {
          "name": "Hour",
          "value": {
            "type": "DateTime",
            "date": "SliceStart",
            "format": "HH"
          }
        }
      ],
      "format": {
        "type": "TextFormat",
        "columnDelimiter": ",",
        "rowDelimiter": "\n"
      }
    },
    "external": true,
    "availability": {
      "frequency": "Hour",
      "interval": 1
    },
    "policy": {
      "externalData": {
        "retryInterval": "00:01:00",
        "retryTimeout": "00:10:00",
        "maximumRetry": 3
      }
    }
  }
}

SQL Server uitvoergegevensset

Het voorbeeld kopieert gegevens naar een tabel met de naam 'MyTable' in SQL Server. Maak de tabel in SQL Server met hetzelfde aantal kolommen als u verwacht dat het CSV-bestand blob bevat. Er worden elk uur nieuwe rijen aan de tabel toegevoegd.

{
  "name": "SqlServerOutput",
  "properties": {
    "type": "SqlServerTable",
    "linkedServiceName": "SqlServerLinkedService",
    "typeProperties": {
      "tableName": "MyOutputTable"
    },
    "availability": {
      "frequency": "Hour",
      "interval": 1
    }
  }
}

Pijplijn met Copy-activiteit

De pijplijn bevat een kopieeractiviteit die is geconfigureerd voor het gebruik van deze invoer- en uitvoergegevenssets en die elk uur moet worden uitgevoerd. In de JSON-definitie van de pijplijn wordt het brontype ingesteld op BlobSource en het sinktype is ingesteld op SqlSink.

{
  "name":"SamplePipeline",
  "properties":{
    "start":"2014-06-01T18:00:00",
    "end":"2014-06-01T19:00:00",
    "description":"pipeline with copy activity",
    "activities":[
      {
        "name": "AzureBlobtoSQL",
        "description": "Copy Activity",
        "type": "Copy",
        "inputs": [
          {
            "name": "AzureBlobInput"
          }
        ],
        "outputs": [
          {
            "name": " SqlServerOutput "
          }
        ],
        "typeProperties": {
          "source": {
            "type": "BlobSource",
            "blobColumnSeparators": ","
          },
          "sink": {
            "type": "SqlSink"
          }
        },
        "scheduler": {
          "frequency": "Hour",
          "interval": 1
        },
        "policy": {
          "concurrency": 1,
          "executionPriorityOrder": "OldestFirst",
          "retry": 0,
          "timeout": "01:00:00"
        }
      }
    ]
  }
}

Verbindingsproblemen oplossen

  1. Configureer uw SQL Server om externe verbindingen te accepteren. Start SQL Server Management Studio, klik met de rechtermuisknop op server en klik op Eigenschappen. Selecteer Verbindingen in de lijst en schakel externe verbindingen met de server toestaan in.

    Externe verbindingen inschakelen

    Zie De optie Serverconfiguratie voor externe toegang configureren voor gedetailleerde stappen.

  2. Start SQL Server Configuration Manager. Vouw SQL Server netwerkconfiguratie uit voor het gewenste exemplaar en selecteer Protocollen voor MSSQLSERVER. U ziet protocollen in het rechterdeelvenster. Schakel TCP/IP in door met de rechtermuisknop op TCP/IP te klikken en op Inschakelen te klikken.

    TCP/IP inschakelen

    Zie Een servernetwerkprotocol in- of uitschakelen voor meer informatie en alternatieve manieren om TCP/IP-protocol in te schakelen.

  3. Dubbelklik in hetzelfde venster op TCP/IP om het venster TCP/IP-eigenschappen te starten.

  4. Schakel over naar het tabblad IP-adressen . Schuif omlaag om de sectie IPAll weer te geven. Noteer de TCP-poort (standaard is 1433).

  5. Maak een regel voor Windows Firewall op de computer om binnenkomend verkeer via deze poort toe te staan.

  6. Verbinding controleren: als u verbinding wilt maken met de SQL Server met behulp van een volledig gekwalificeerde naam, gebruikt u SQL Server Management Studio vanaf een andere computer. Bijvoorbeeld: "<machine>.< domain.corp>.< company.com,1433>."

    Belangrijk

    Zie Gegevens verplaatsen tussen on-premises bronnen en de cloud met Gegevensbeheer Gateway voor gedetailleerde informatie.

    Zie Gatewayproblemen oplossen voor tips over het oplossen van verbindingsproblemen/gatewaygerelateerde problemen.

Identiteitskolommen in de doeldatabase

In deze sectie vindt u een voorbeeld van het kopiëren van gegevens uit een brontabel zonder identiteitskolom naar een doeltabel met een identiteitskolom.

Brontabel:

create table dbo.SourceTbl
(
    name varchar(100),
    age int
)

Doeltabel:

create table dbo.TargetTbl
(
    identifier int identity(1,1),
    name varchar(100),
    age int
)

U ziet dat de doeltabel een identiteitskolom heeft.

JSON-definitie van brongegevensset

{
    "name": "SampleSource",
    "properties": {
        "published": false,
        "type": " SqlServerTable",
        "linkedServiceName": "TestIdentitySQL",
        "typeProperties": {
            "tableName": "SourceTbl"
        },
        "availability": {
            "frequency": "Hour",
            "interval": 1
        },
        "external": true,
        "policy": {}
    }
}

JSON-definitie van doelgegevensset

{
    "name": "SampleTarget",
    "properties": {
        "structure": [
            { "name": "name" },
            { "name": "age" }
        ],
        "published": false,
        "type": "AzureSqlTable",
        "linkedServiceName": "TestIdentitySQLSource",
        "typeProperties": {
            "tableName": "TargetTbl"
        },
        "availability": {
            "frequency": "Hour",
            "interval": 1
        },
        "external": false,
        "policy": {}
    }
}

U ziet dat als de bron- en doeltabel een ander schema hebben (het doel heeft een extra kolom met identiteit). In dit scenario moet u de structuureigenschap opgeven in de definitie van de doelgegevensset, die niet de identiteitskolom bevat.

Opgeslagen procedure aanroepen vanuit SQL Sink

Zie Het artikel over het aanroepen van een opgeslagen procedure voor SQL Sink in het artikel over kopieeractiviteit voor een voorbeeld van het aanroepen van een opgeslagen procedure vanuit SQL Sink in een kopieeractiviteit van een pijplijn.

Typetoewijzing voor SQL Server

Zoals vermeld in het artikel over activiteiten voor gegevensverplaatsing, voert de Copy-activiteit automatische typeconversies uit van brontypen naar sinktypen met de volgende tweestapsbenadering:

  1. Converteren van systeemeigen brontypen naar .NET-type
  2. Converteren van .NET-type naar systeemeigen sinktype

Wanneer u gegevens verplaatst van & SQL Server, worden de volgende toewijzingen gebruikt van het SQL-type naar het .NET-type en omgekeerd.

De toewijzing is hetzelfde als de SQL Server gegevenstypetoewijzing voor ADO.NET.

type SQL Server database-engine .NET Framework type
bigint Int64
binair Byte[]
bit Booleaans
char Tekenreeks, Teken[]
date DateTime
Datum/tijd DateTime
datetime2 DateTime
Datetimeoffset DateTimeOffset
Decimaal Decimaal
FILESTREAM-kenmerk (varbinary(max)) Byte[]
Float Dubbel
image Byte[]
int Int32
money Decimaal
nchar Tekenreeks, Teken[]
ntext Tekenreeks, Teken[]
numeriek Decimaal
nvarchar Tekenreeks, Teken[]
werkelijk Enkelvoudig
rowversion Byte[]
smalldatetime DateTime
smallint Int16
smallmoney Decimaal
sql_variant Object *
tekst Tekenreeks, Teken[]
tijd TimeSpan
tijdstempel Byte[]
tinyint Byte
uniqueidentifier Guid
varbinary Byte[]
varchar Tekenreeks, Teken[]
xml Xml

Bron toewijzen aan sinkkolommen

Zie Kolommen voor toewijzingsgegevenssets in Azure Data Factory als u kolommen van de brongegevensset wilt toewijzen aan kolommen uit de sinkgegevensset.

Herhaalbare kopie

Wanneer u gegevens kopieert naar SQL Server Database, worden met de kopieeractiviteit standaard gegevens aan de sinktabel toegevoegd. Als u in plaats daarvan een UPSERT wilt uitvoeren, raadpleegt u het artikel Herhaalbare schrijfbewerking naar SqlSink .

Houd bij het kopiëren van gegevens uit relationele gegevensarchieven rekening met herhaalbaarheid om onbedoelde resultaten te voorkomen. In Azure Data Factory kunt u een segment handmatig opnieuw uitvoeren. U kunt ook beleid voor opnieuw proberen configureren voor een gegevensset, zodat een segment opnieuw wordt uitgevoerd wanneer er een fout optreedt. Wanneer een segment op beide manieren opnieuw wordt uitgevoerd, moet u ervoor zorgen dat dezelfde gegevens worden gelezen, ongeacht hoe vaak een segment wordt uitgevoerd. Zie Herhaalbaar lezen uit relationele bronnen.

Prestaties en afstemming

Zie de handleiding voor het afstemmen van de prestaties & van kopieeractiviteiten voor meer informatie over belangrijke factoren die van invloed zijn op de prestaties van gegevensverplaatsing (kopieeractiviteit) in Azure Data Factory en op verschillende manieren om deze te optimaliseren.