Verschieben von Daten in und aus SQL Server mithilfe von Azure Data Factory

Hinweis

Dieser Artikel gilt für Version 1 von Data Factory. Wenn Sie die aktuelle Version des Data Factory-Diensts verwenden, finden Sie weitere Informationen unter SQL Server-Connector in V2.

Dieser Artikel beschreibt, wie Sie die Kopieraktivität in Azure Data Factory verwenden, um Daten in und aus einer SQL Server-Datenbank zu verschieben. Dieser Artikel baut auf dem Artikel zu Datenverschiebungsaktivitäten auf, der eine allgemeine Übersicht zur Datenverschiebung mit der Kopieraktivität bietet.

Hinweis

Für die Interaktion mit Azure wird das PowerShell-Modul „Azure Az“ empfohlen. Informationen zu den ersten Schritten finden Sie unter Installieren des Azure Az PowerShell-Moduls. Informationen zum Migrieren zum Az PowerShell-Modul finden Sie unter Migrieren von Azure PowerShell von AzureRM zum Az-Modul.

Unterstützte Szenarios

Sie können Daten aus einer SQL Server-Datenbank in die folgenden Datenspeicher kopieren:

Category Datenspeicher
Azure Azure Blob Storage
Azure Data Lake Storage Gen1
Azure Cosmos DB (SQL-API)
Azure SQL-Datenbank
Azure Synapse Analytics
Azure Cognitive Search-Index
Azure Table Storage
Datenbanken SQL Server
Oracle
Datei Dateisystem

Sie können Daten aus den folgenden Datenspeichern in eine SQL Server-Datenbank kopieren:

Category Datenspeicher
Azure Azure Blob Storage
Azure Cosmos DB (SQL-API)
Azure Data Lake Storage Gen1
Azure SQL-Datenbank
Azure Synapse Analytics
Azure Table Storage
Datenbanken Amazon Redshift
DB2
MySQL
Oracle
PostgreSQL
SAP Business Warehouse
SAP HANA
SQL Server
Sybase
Teradata
NoSQL Cassandra
MongoDB
Datei Amazon S3
Dateisystem
FTP
HDFS
SFTP
Andere Generisches HTTP
Generisches OData
Generische ODBC
Salesforce
Webtabelle (HTML-Tabelle)

Unterstützte SQL Server-Versionen

Dieser SQL Server-Connector unterstützt das Kopieren von Daten von den bzw. in die folgenden Versionen der lokal oder in Azure IaaS gehosteten Instanz mit SQL-Authentifizierung und Windows-Authentifizierung: SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005.

Herstellen der Verbindung

Die Konzepte und Schritte zum Herstellen einer Verbindung mit SQL Server, ob lokal oder in Azure-IaaS-VMs (Infrastructure-as-a-Service) gehostet, sind identisch. In beiden Fällen müssen Sie das Datenverwaltungsgateway zum Herstellen der Verbindung nutzen.

Im Artikel Verschieben von Daten zwischen lokalen Standorten und Cloud erfahren mehr zum Datenverwaltungsgateway und erhalten eine schrittweise Anleitung zum Einrichten des Gateways. Das Einrichten einer Gatewayinstanz ist eine Voraussetzung für die Verbindung mit SQL Server.

Wenngleich Sie das Gateway auf demselben lokalen Computer oder virtuellen Cloudcomputer wie SQL Server installieren können, wird aus Leistungsgründen empfohlen, diese Komponenten auf getrennten Computern zu installieren. Wenn sich Gateway und SQL Server auf getrennten Computern befinden, werden Ressourcenkonflikte reduziert.

Erste Schritte

Sie können eine Pipeline mit einer Kopieraktivität erstellen, die Daten mithilfe verschiedener Tools/APIs in und aus einer SQL Server-Datenbank verschiebt.

Am einfachsten erstellen Sie eine Pipeline mit dem Kopier-Assistenten. Siehe Tutorial: Erstellen einer Pipeline mit dem Kopier-Assistenten finden Sie eine kurze exemplarische Vorgehensweise zum Erstellen einer Pipeline mithilfe des Assistenten zum Kopieren von Daten.

Sie können auch die folgenden Tools zum Erstellen einer Pipeline verwenden: Visual Studio, Azure PowerShell, Azure Resource Manager-Vorlage, .NET-API und REST-API. Im Tutorial zur Kopieraktivität finden Sie detaillierte Anweisungen, wie Sie eine Pipeline mit einer Kopieraktivität erstellen können.

Unabhängig davon, ob Sie Tools oder APIs verwenden, führen Sie die folgenden Schritte aus, um eine Pipeline zu erstellen, die Daten aus einem Quelldatenspeicher in einen Senkendatenspeicher verschiebt:

  1. Eine Data Factory. Eine Data Factory kann eine oder mehrere Pipelines enthalten.
  2. Erstellen verknüpfter Dienste zum Verknüpfen von Eingabe- und Ausgabedatenspeichern mit Ihrer Data Factory. Wenn Sie beispielsweise Daten aus einer SQL Server-Datenbank in Azure Blob Storage kopieren, erstellen Sie zwei verknüpfte Dienste, um Ihre SQL Server-Datenbank und Ihr Azure-Speicherkonto mit Ihrer Data Factory zu verknüpfen. Informationen zu Eigenschaften von verknüpften Diensten, die spezifisch für eine SQL Server-Datenbank sind, finden Sie im Abschnitt Eigenschaften des verknüpften Diensts.
  3. Erstellen von Datasets zur Darstellung von Eingabe- und Ausgabedaten für den Kopiervorgang. Im Beispiel, das im letzten Schritt erwähnt wurde, erstellen Sie ein Dataset, um die SQL-Tabelle mit den Eingabedaten in Ihrer SQL Server-Datenbank anzugeben. Außerdem erstellen Sie ein weiteres Dataset zum Angeben eines Blobcontainers und des Ordners, in dem die aus der SQL Server-Datenbank kopierten Daten enthalten sind. Informationen zu Dataset-Eigenschaften, die spezifisch für die SQL Server-Datenbank sind, finden Sie im Abschnitt Dataset-Eigenschaften.
  4. Erstellen einer Pipeline mit einer Kopieraktivität, die ein Dataset als Eingabe und ein Dataset als Ausgabe akzeptiert. Im oben erwähnten Beispiel verwenden Sie „SqlSource“ als Quelle und „BlobSink“ als Senke für die Kopieraktivität. Wenn Sie einen Kopiervorgang von Azure Blob Storage zur SQL Server-Datenbank durchführen, verwenden Sie entsprechend „BlobSource“ und „SqlSink“ in der Kopieraktivität. Informationen zu den Eigenschaften von Kopieraktivitäten, die spezifisch für die SQL Server-Datenbank sind, finden Sie im Abschnitt Eigenschaften der Kopieraktivität. Ausführliche Informationen zur Verwendung eines Datenspeichers als Quelle oder Senke erhalten Sie, indem Sie im vorherigen Abschnitt auf den Link für Ihren Datenspeicher klicken.

Wenn Sie den Assistenten verwenden, werden automatisch JSON-Definitionen für diese Data Factory-Entitäten (verknüpfte Diensten, Datasets und die Pipeline) erstellt. Bei Verwendung von Tools und APIs (mit Ausnahme der .NET-API) definieren Sie diese Data Factory-Entitäten im JSON-Format. Beispiele mit JSON-Definitionen für Data Factory-Entitäten für das Kopieren von Daten in und aus einer SQL Server-Datenbank finden Sie in diesem Artikel im Abschnitt JSON-Beispiele.

Die folgenden Abschnitte enthalten Details zu JSON-Eigenschaften, die zum Definieren von Data Factory-Entitäten speziell für SQL Server verwendet werden:

Eigenschaften des verknüpften Diensts

Sie erstellen einen verknüpften Dienst des Typs OnPremisesSqlServer, um eine SQL Server-Datenbank mit einer Data Factory zu verknüpfen. Die folgende Tabelle enthält eine Beschreibung der JSON-Elemente, die für den mit SQL Server verknüpften Dienst spezifisch sind.

Die folgende Tabelle enthält eine Beschreibung der JSON-Elemente, die für den mit SQL Server verknüpften Dienst spezifisch sind.

Eigenschaft Beschreibung Erforderlich
type Legen Sie die type-Eigenschaft auf OnPremisesSqlServer fest. Ja
connectionString Geben Sie „connectionString“-Informationen an, die zum Herstellen einer Verbindung mit der SQL Server-Datenbank mithilfe der SQL- oder Windows-Authentifizierung benötigt werden. Ja
gatewayName Name des Gateways, das der Data Factory-Dienst zum Herstellen einer Verbindung mit der SQL Server-Datenbank verwenden soll. Ja
username Geben Sie den Benutzernamen an, wenn Sie die Windows-Authentifizierung verwenden. Beispiel: domainname\username. Nein
password Geben Sie das Kennwort für das Benutzerkonto an, das Sie für den Benutzernamen angegeben haben. Nein

Sie können Anmeldeinformationen mithilfe des Cmdlets New-AzDataFactoryEncryptValue verschlüsseln und wie im folgenden Beispiel gezeigt in der Verbindungszeichenfolge verwenden (EncryptedCredential-Eigenschaft):

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

Beispiele

JSON für die SQL Server-Authentifizierung

{
    "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 für die Windows-Authentifizierung

Das Datenverwaltungsgateway übernimmt die Identität des angegebenen Benutzerkontos und stellt eine Verbindung mit der SQL Server-Datenbank her.

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

Dataset-Eigenschaften

In den Beispielen haben Sie ein Dataset des Typs SqlServerTable verwendet, um eine Tabelle in einer SQL Server-Datenbank darzustellen.

Eine vollständige Liste mit den Abschnitten & Eigenschaften, die zum Definieren von Datasets zur Verfügung stehen, finden Sie unter Erstellen von Datasets. Abschnitte wie „structure“, „availability“ und „policy“ der JSON eines Datasets sind bei allen Arten von Datasets (SQL Server, Azure-Blob, Azure-Tabelle usw.) ähnlich.

Der Abschnitt "typeProperties" unterscheidet sich bei jedem Typ von Dataset und bietet Informationen zum Speicherort der Daten im Datenspeicher. Der Abschnitt typeProperties für ein Dataset des Typs SqlServerTable hat die folgenden Eigenschaften:

Eigenschaft Beschreibung Erforderlich
tableName Name der Tabelle oder Ansicht in der SQL Server-Datenbankinstanz, auf die der verknüpfte Dienst verweist. Ja

Eigenschaften der Kopieraktivität

Wenn Sie Daten aus einer SQL Server-Datenbank verschieben, legen Sie den Quelltyp in der Kopieraktivität auf SqlSourcefest. Wenn Sie Daten in eine SQL Server-Datenbank verschieben, legen Sie den Senkentyp in der Kopieraktivität auf SqlSinkfest. Dieser Abschnitt enthält eine Liste der Eigenschaften, die von „SqlSource“ und „SqlSink“ unterstützt werden.

Eine vollständige Liste der Abschnitte und Eigenschaften zum Definieren von Aktivitäten finden Sie im Artikel Pipelines und Aktivitäten in Azure Data Factory. Eigenschaften wie Name, Beschreibung, Eingabe- und Ausgabetabellen und Richtlinien sind für alle Arten von Aktivitäten verfügbar.

Hinweis

Die Kopieraktivität verwendet nur eine Eingabe und erzeugt nur eine Ausgabe.

Eigenschaften im Abschnitt typeProperties der Aktivität können dagegen je nach Aktivitätstyp variieren. Für die Kopieraktivität variieren die Eigenschaften je nach Art der Quellen und Senken.

SqlSource

Wenn bei einer Kopieraktivität die Quelle den Typ SqlSource aufweist, sind im Abschnitt typeProperties die folgenden Eigenschaften verfügbar:

Eigenschaft BESCHREIBUNG Zulässige Werte Erforderlich
sqlReaderQuery Verwendet die benutzerdefinierte Abfrage zum Lesen von Daten. SQL-Abfragezeichenfolge. Beispiel: select * from MyTable. Kann auf mehrere Tabellen aus der Datenbank verweisen, auf die vom Eingabedataset verwiesen wird. Falls nicht angegeben, wird folgende SQL-Anweisung ausgeführt: "select from MyTable". Nein
sqlReaderStoredProcedureName Der Name der gespeicherten Prozedur, die Daten aus der Quelltabelle liest. Name der gespeicherten Prozedur. Die letzte SQL-Anweisung muss eine SELECT-Anweisung in der gespeicherten Prozedur sein. Nein
storedProcedureParameters Parameter für die gespeicherte Prozedur. Name-Wert-Paare. Die Namen und die Groß-/Kleinschreibung von Parametern müssen denen der Parameter der gespeicherten Prozedur entsprechen. Nein

Wenn sqlReaderQuery für SqlSource angegeben ist, führt die Kopieraktivität diese Abfrage in der SQL Server-Datenbankquelle aus, um die Daten abzurufen.

Alternativ dazu können Sie eine gespeicherte Prozedur angeben, indem Sie sqlReaderStoredProcedureName und storedProcedureParameters angeben (sofern die gespeicherten Prozeduren Parameter verwenden).

Wenn Sie weder sqlReaderQuery noch sqlReaderStoredProcedureName angeben, werden die im Strukturabschnitt definierten Spalten verwendet, um eine SELECT-Abfrage zur Ausführung in der SQL Server-Datenbank zu erstellen. Falls die DataSet-Definition nicht über die Struktur verfügt, werden alle Spalten der Tabelle ausgewählt.

Hinweis

Bei Verwendung von sqlReaderStoredProcedureName müssen Sie trotzdem einen Wert für die tableName-Eigenschaft in der Dataset-JSON angeben. Es finden jedoch keine Überprüfungen dieser Tabelle statt.

SqlSink

SqlSink unterstützt die folgenden Eigenschaften:

Eigenschaft BESCHREIBUNG Zulässige Werte Erforderlich
writeBatchTimeout Die Wartezeit für den Abschluss der Batcheinfügung, bis das Timeout wirksam wird. Zeitraum

Beispiel: „00:30:00“ (30 Minuten).
Nein
writeBatchSize Fügt Daten in die SQL-Tabelle ein, wenn die Puffergröße "writeBatchSize" erreicht. Integer (Gesamtanzahl von Zeilen) Nein (Standardwert: 10.000)
sqlWriterCleanupScript Geben Sie die Abfrage für die Kopieraktivität so an, dass bei Ausführung die Daten eines bestimmten Slices bereinigt werden. Weitere Informationen finden Sie im Abschnitt Wiederholbare Kopiervorgänge. Eine Abfrageanweisung. Nein
sliceIdentifierColumnName Geben Sie einen Spaltennamen an, den die Kopieraktivität mit einem automatisch generierten Slicebezeichner füllen soll, der bei erneuter Ausführung zum Bereinigen der Daten eines bestimmten Slices verwendet wird. Weitere Informationen finden Sie im Abschnitt Wiederholbare Kopiervorgänge. Spaltenname einer Spalte mit binärem Datentyp (32). Nein
sqlWriterStoredProcedureName Der Name der gespeicherten Prozedur, die definiert, wie Quelldaten in der Zieltabelle angewendet werden (etwa durch Ausführen von Upserts oder Transformationen mit eigener Geschäftslogik).

Beachten Sie, dass diese gespeicherte Prozedur pro Batch aufgerufen wird. Verwenden Sie bei einem Vorgang, der nur einmal ausgeführt wird und nicht mit Quelldaten in Zusammenhang steht (etwa Löschen/Kürzen), die sqlWriterCleanupScript-Eigenschaft.
Name der gespeicherten Prozedur. Nein
storedProcedureParameters Parameter für die gespeicherte Prozedur. Name-Wert-Paare. Die Namen und die Groß-/Kleinschreibung von Parametern müssen denen der Parameter der gespeicherten Prozedur entsprechen. Nein
sqlWriterTableType Geben Sie einen Tabellentypnamen an, der in der gespeicherten Prozedur verwendet werden soll. Die Kopieraktivität macht die verschobenen Daten in einer temporären Tabelle mit diesem Tabellentyp verfügbar. Der gespeicherte Prozedurcode kann dann die kopierten Daten mit vorhandenen Daten zusammenführen. Ein Tabellentypname. Nein

JSON-Beispiele zum Kopieren von Daten aus und in SQL Server

Die folgenden Beispiele zeigen JSON-Beispieldefinitionen, die Sie zum Erstellen einer Pipeline mit Visual Studio oder Azure PowerShell verwenden können. In den folgenden Beispielen wird veranschaulicht, wie Sie Daten in und aus SQL Server und Azure Blob Storage kopieren. Daten können jedoch mithilfe der Kopieraktivität in Azure Data Factory direkt aus beliebigen Quellen in die hier aufgeführten Senken kopiert werden.

Beispiel: Kopieren von Daten aus SQL Server in ein Azure-Blob

Dieses Beispiel zeigt Folgendes:

  1. Einen verknüpften Dienst des Typs OnPremisesSqlServer
  2. Einen verknüpften Dienst des Typs AzureStorage
  3. Ein Eingabedataset des Typs SqlServerTable.
  4. Ein Ausgabedataset des Typs AzureBlob
  5. Eine Pipeline mit Kopieraktivität, die SqlSource und BlobSink verwendet

Im Beispiel werden Zeitreihendaten aus einer SQL Server-Tabelle stündlich in einen Azure-Blob kopiert. Die bei diesen Beispielen verwendeten JSON-Eigenschaften werden in den Abschnitten beschrieben, die auf die Beispiele folgen.

Als Erstes richten Sie das Datenverwaltungsgateway ein. Anweisungen dazu finden Sie im Artikel Verschieben von Daten zwischen lokalen Standorten und Cloud .

Mit SQL Server verknüpfter Dienst

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

Mit Azure Blob Storage verknüpfter Dienst

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

SQL Server-Eingabedataset

Im Beispiel wird davon ausgegangen, dass Sie eine Tabelle namens „MyTable“ in SQL Server erstellt haben und dass sie eine Spalte namens „timestampcolumn“ für Zeitreihendaten enthält. Sie können mehrere Tabellen in derselben Datenbank mithilfe eines einzigen Datasets abfragen, aber für die typeProperty-Eigenschaft tableName des Datasets muss eine einzelne Tabelle verwendet werden.

Durch Festlegen von „external“ auf „true“ wird dem Data Factory-Dienst mitgeteilt, dass das Dataset für die Data Factory extern ist und nicht durch eine Aktivität in der Data Factory erzeugt wird.

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

Daten werden stündlich in ein neues Blob geschrieben (frequency: hour, interval: 1). Der Ordnerpfad des Blobs wird basierend auf der Startzeit des Slices, der verarbeitet wird, dynamisch ausgewertet. Im Ordnerpfad werden Jahr, Monat, Tag und die Stundenteile der Startzeit verwendet.

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

Pipeline mit Kopieraktivität

Die Pipeline enthält eine Kopieraktivität, die für das Verwenden der Ein- und Ausgabedatasets und für eine stündliche Ausführung konfiguriert ist. In der JSON-Definition der Pipeline ist der Typ source auf SqlSource und der Typ sink auf BlobSink festgelegt. Die für die SqlReaderQuery -Eigenschaft angegebene SQL-Abfrage wählt die Daten der letzten Stunde zum Kopieren aus.

{
  "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 diesem Beispiel ist sqlReaderQuery für SqlSource angegeben. Mit der Kopieraktivität wird diese Abfrage in der SQL Server-Datenbankquelle ausgeführt, um die Daten abzurufen. Alternativ dazu können Sie eine gespeicherte Prozedur angeben, indem Sie sqlReaderStoredProcedureName und storedProcedureParameters angeben (sofern die gespeicherten Prozeduren Parameter verwenden). sqlReaderQuery kann auf mehrere Tabellen in der Datenbank verweisen, die vom Eingabedataset referenziert wird. Die Eigenschaft ist im Gegensatz zur typeProperty-Eigenschaft tableName des Datasets nicht auf den Tabellensatz beschränkt.

Wenn Sie sqlReaderQuery oder sqlReaderStoredProcedureName nicht angeben, werden die im Strukturabschnitt definierten Spalten verwendet, um eine SELECT-Abfrage zur Ausführung in der SQL Server-Datenbank zu erstellen. Falls die DataSet-Definition nicht über die Struktur verfügt, werden alle Spalten der Tabelle ausgewählt.

Eine Liste mit den Eigenschaften, die von SqlSource und BlobSink unterstützt werden, finden Sie im Abschnitt Sql Source und unter BlobSink.

Beispiel: Kopieren von Daten aus einem Azure-Blob in SQL Server

Dieses Beispiel zeigt Folgendes:

  1. Den verknüpften Dienst des Typs OnPremisesSqlServer
  2. Den verknüpften Dienst des Typs AzureStorage
  3. Ein Eingabedataset des Typs AzureBlob
  4. Ein Ausgabedataset des Typs SqlServerTable
  5. Die Pipeline mit einer Kopieraktivität, die BlobSource und SqlSink verwendet.

Im Beispiel werden Zeitreihendaten aus einem Azure-Blob stündlich in eine SQL Server-Tabelle kopiert. Die bei diesen Beispielen verwendeten JSON-Eigenschaften werden in den Abschnitten beschrieben, die auf die Beispiele folgen.

Mit SQL Server verknüpfter Dienst

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

Mit Azure Blob Storage verknüpfter Dienst

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

Azure-Blob-Eingabedataset

Daten werden stündlich aus einem neuen Blob übernommen (frequency: hour, interval: 1). Ordnerpfad und Dateiname des Blobs werden basierend auf der Startzeit des Slices, der verarbeitet wird, dynamisch ausgewertet. Der Ordnerpfad verwendet die Bestandteile Jahr, Monat und Tag der Startzeit, und der Dateiname verwendet die Stunde der Startzeit. Die Festlegung von „external“ auf „true“ teilt dem Data Factory-Dienst mit, dass dieses Dataset für die Data Factory extern ist und nicht durch eine Aktivität in der Data Factory erzeugt wird.

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

Das Beispiel kopiert Daten in eine Tabelle namens „MyTable“ in SQL Server. Erstellen Sie die Tabelle in SQL Server mit der gleichen Anzahl von Spalten, die Sie in der CSV-Datei im Blob erwarten. Neue Zeilen werden der Tabelle stündlich hinzugefügt.

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

Pipeline mit Kopieraktivität

Die Pipeline enthält eine Kopieraktivität, die für das Verwenden der Ein- und Ausgabedatasets und für eine stündliche Ausführung konfiguriert ist. In der JSON-Definition der Pipeline ist der Typ source auf BlobSource und der Typ sink auf SqlSink festgelegt.

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

Beheben von Verbindungsproblemen

  1. Konfigurieren Sie Ihren SQL-Server, um Remoteverbindungen zu akzeptieren. Starten Sie SQL Server Management Studio, klicken Sie mit der rechten Maustaste auf Server, und klicken Sie dann auf Eigenschaften. Wählen Sie in der Liste den Eintrag Verbindungen aus, und aktivieren Sie das Kontrollkästchen Remoteverbindungen mit diesem Server zulassen.

    Aktivieren von Remoteverbindungen

    Ausführliche Schritte finden Sie im Abschnitt zum Konfigurieren der Serverkonfigurationsoption für den Remotezugriff .

  2. Starten Sie den SQL Server-Konfigurations-Manager. Erweitern Sie SQL Server-Netzwerkkonfiguration für die gewünschte Instanz, und wählen Sie Protokolle für MSSQLSERVER aus. Im rechten Bereich werden Protokolle angezeigt. Aktivieren Sie TCP/IP, indem Sie mit der rechten Maustaste auf TCP/IP klicken und anschließend auf Aktivieren klicken.

    Aktivieren von TCP/IP

    Weitere Methoden zum Aktivieren des TCP/IP-Protokolls finden Sie unter Aktivieren oder Deaktivieren eines Servernetzwerkprotokolls .

  3. Doppelklicken Sie im gleichen Fenster auf TCP/IP, um das Fenster TCP/IP-Eigenschaften zu öffnen.

  4. Wechseln Sie zur Registerkarte IP-Adressen . Scrollen Sie nach unten zum Abschnitt IPAll . Notieren Sie sich den TCP-Port (Standardwert ist 1433).

  5. Erstellen Sie auf dem Computer eine Regel für die Windows-Firewall , um eingehenden Datenverkehr über diesen Port zuzulassen.

  6. Überprüfen der Verbindung: Verwenden Sie SQL Server Management Studio auf einem anderen Computer, um mit dem vollqualifizierten Namen eine Verbindung mit der SQL Server-Instanz herzustellen. Beispiel: „<Computer>.<Domäne>.corp.<Unternehmen>.com, 1433.“

    Wichtig

    Ausführliche Informationen finden Sie unter Verschieben von Daten zwischen lokalen Quellen und der Cloud mit dem Datenverwaltungsgateway.

    Unter Problembehandlung bei Gateways finden Sie Tipps zur Behandlung von Verbindungs- bzw. Gatewayproblemen.

Identitätsspalten in der Zieldatenbank

Dieser Abschnitt enthält ein Beispiel zum Kopieren von Daten aus einer Quelltabelle ohne eine Identitätsspalte in eine Zieltabelle mit einer Identitätsspalte.

Quelltabelle:

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

Zieltabelle:

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

Beachten Sie, dass die Zieltabelle über eine Identitätsspalte verfügt.

Definition der Quell-Dataset-JSON

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

Definition der Ziel-Dataset-JSON

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

Beachten Sie, dass die Quell- und die Zieltabelle unterschiedliche Schemas besitzen (das Ziel verfügt über eine zusätzliche Spalte mit der Identität). In diesem Szenario müssen Sie eine structure-Eigenschaft in der Definition des Zieldatasets angeben, die nicht die Identitätsspalte enthält.

Aufrufen der gespeicherten Prozedur von der SQL-Senke

Im Artikel Aufrufen der gespeicherten Prozedur für die SQL-Senke in einer Kopieraktivität finden Sie ein Beispiel für den Aufruf einer gespeicherten Prozedur von der SQL-Senke in einer Kopieraktivität einer Pipeline.

Typzuordnung für SQL Server

Wie im Artikel Datenverschiebungsaktivitäten beschrieben, führt die Kopieraktivität automatische Typkonvertierungen von Quelltypen in Senkentypen mithilfe des folgenden aus zwei Schritten bestehenden Ansatzes durch:

  1. Konvertieren von systemeigenen Quelltypen in den .NET-Typ
  2. Konvertieren vom .NET-Typ in systemeigenen Senkentyp

Beim Verschieben von Daten in und aus SQL Server werden die folgenden Zuordnungen zwischen SQL-Typ und .NET-Typ und umgekehrt verwendet.

Die Zuordnung ist mit der SQL Server-Datentypzuordnung für ADO.NET identisch.

Typ „SQL Server-Datenbank-Engine“ .NET Framework-Typ
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
image 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 Byte
UNIQUEIDENTIFIER Guid
varbinary Byte[]
varchar String, Char[]
Xml Xml

Zuordnen von Quellspalten zu Senkenspalten

Weitere Informationen zum Zuordnen von Spalten im Quelldataset zu Spalten im Senkendataset finden Sie unter Zuordnen von Datasetspalten in Azure Data Factory.

Wiederholbare Kopiervorgänge

Beim Kopieren von Daten in SQL Server-Datenbank fügt die Kopieraktivität standardmäßig Daten an die Senkentabelle an. Wenn Sie stattdessen einen UPSERT-Vorgang ausführen möchten, lesen Sie den Artikel Wiederholbare Schreibvorgänge in SqlSink.

Beim Kopieren von Daten aus relationalen Datenspeichern müssen Sie die Wiederholbarkeit berücksichtigen, um unbeabsichtigte Ergebnisse zu vermeiden. Sie können einen Slice in Azure Data Factory manuell erneut ausführen. Sie können auch eine Wiederholungsrichtlinie für ein Dataset konfigurieren, sodass ein Slice erneut ausgeführt wird, wenn ein Fehler auftritt. Wenn ein Slice erneut ausgeführt wird, müssen Sie sicherstellen, dass dieselben Daten gelesen werden – egal wie oft ein Slice ausgeführt wird. Weitere Informationen finden Sie unter Wiederholbare Lesevorgänge aus relationalen Quellen.

Leistung und Optimierung

Der Artikel Handbuch zur Leistung und Optimierung& der Kopieraktivität beschreibt wichtige Faktoren, die sich auf die Leistung der Datenverschiebung (Kopieraktivität) in Azure Data Factory auswirken, sowie verschiedene Möglichkeiten zur Leistungsoptimierung.