Delen via

Opzoektransformatie

Van toepassing op:SQL Server SSIS Integration Runtime in Azure Data Factory

Met de opzoektransformatie worden zoekopdrachten uitgevoerd door gegevens in invoerkolommen samen te voegen met kolommen in een referentiegegevensset. U gebruikt de zoekactie voor toegang tot aanvullende informatie in een gerelateerde tabel die is gebaseerd op waarden in gemeenschappelijke kolommen.

De referentiegegevensset kan een cachebestand, een bestaande tabel of weergave, een nieuwe tabel of het resultaat van een SQL-query zijn. De opzoektransformatie maakt gebruik van een OLE DB-verbindingsbeheer of een cacheverbindingsbeheer om verbinding te maken met de referentiegegevensset. Zie OLE DB Connection Manager en Cache Connection Manager voor meer informatie

U kunt de opzoektransformatie op de volgende manieren configureren:

  • Selecteer het verbindingsbeheer dat u wilt gebruiken. Als u verbinding wilt maken met een database, selecteert u een OLE DB-verbindingsbeheer. Als u verbinding wilt maken met een cachebestand, selecteert u een cacheverbindingsbeheer.

  • Geef de tabel of weergave op die de referentiegegevensset bevat.

  • Genereer een referentiegegevensset door een SQL-instructie op te geven.

  • Geef joins op tussen de invoer en de referentiegegevensset.

  • Voeg kolommen uit de referentiegegevensset toe aan de uitvoer van de lookup-transformatie.

  • Configureer de cacheopties.

De lookup-transformatie ondersteunt de volgende databaseproviders voor het OLE DB-verbindingsbeheer:

  • SQL Server

  • Oracle

  • DB2

De lookup-transformatie probeert een equi-join uit te voeren tussen waarden in de transformatie-invoer en -waarden in de referentiegegevensset. (Een equi-join betekent dat elke rij in de transformatie-invoer moet overeenkomen met ten minste één rij uit de referentiegegevensset.) Als een equi-join niet mogelijk is, voert de lookup-transformatie een van de volgende acties uit:

  • Als er geen overeenkomende vermelding in de referentiegegevensset staat, vindt er geen join plaats. De opzoektransformatie behandelt standaard rijen zonder overeenkomende vermeldingen als fouten. U kunt de opzoektransformatie echter zo configureren dat dergelijke rijen worden omgeleid naar een uitvoer zonder overeenkomst.

  • Als de verwijzingstabel meerdere overeenkomsten bevat, retourneert de opzoektransformatie alleen de eerste overeenkomst die door de opzoekquery wordt geretourneerd. Als er meerdere overeenkomsten worden gevonden, genereert de opzoektransformatie alleen een fout of waarschuwing wanneer de transformatie is geconfigureerd voor het laden van alle referentiegegevenssets in de cache. In dit geval genereert de opzoektransformatie een waarschuwing wanneer de transformatie meerdere overeenkomsten detecteert wanneer de transformatie de cache vult.

De join kan een samengestelde join zijn, wat betekent dat u meerdere kolommen in de transformatie-invoer kunt toevoegen aan kolommen in de referentiegegevensset. De transformatie ondersteunt joinkolommen met elk gegevenstype, met uitzondering van DT_R4, DT_R8, DT_TEXT, DT_NTEXT of DT_IMAGE. Zie Integration Services-gegevenstypen voor meer informatie.

Normaal gesproken worden waarden uit de referentiegegevensset toegevoegd aan de transformatie-uitvoer. De opzoektransformatie kan bijvoorbeeld een productnaam uit een tabel extraheren met behulp van een waarde uit een invoerkolom en vervolgens de productnaam toevoegen aan de transformatie-uitvoer. De waarden uit de referentietabel kunnen kolomwaarden vervangen of aan nieuwe kolommen worden toegevoegd.

De zoekacties die door de lookup-transformatie worden uitgevoerd, zijn hoofdlettergevoelig. Als u opzoekfouten wilt voorkomen die worden veroorzaakt door verschillen in hoofdletters of kleine letters in gegevens, gebruikt u eerst de transformatie tekentoewijzing om de gegevens te converteren naar hoofdletters of kleine letters. Neem vervolgens de functies UPPER of LOWER op in de SQL-instructie waarmee de verwijzingstabel wordt gegenereerd. Zie Tekenkaarttransformatie, UPPER (Transact-SQL) en LOWER (Transact-SQL) voor meer informatie.

De opzoektransformatie heeft de volgende invoer en uitvoer:

  • Invoer.

  • Overeenkomende uitvoer. De uitvoer van de overeenkomst verwerkt de rijen in de transformatie-invoer die overeenkomen met ten minste één vermelding in de referentiegegevensset.

  • Geen match-uitvoer. De uitvoer zonder overeenkomst verwerkt rijen in de invoer die niet overeenkomen met ten minste één vermelding in de referentiegegevensset. Als u de opzoektransformatie configureert om de rijen te behandelen zonder overeenkomende vermeldingen als fouten, worden de rijen omgeleid naar de foutuitvoer. Anders zou de transformatie deze rijen omleiden naar de uitvoer zonder overeenkomst.

  • Foutuitvoer.

De referentiegegevensset opslaan in de cache

In een cache in het geheugen wordt de referentiegegevensset opgeslagen en wordt een hash-tabel opgeslagen waarmee de gegevens worden geïndexeert. De cache blijft in het geheugen totdat de uitvoering van het pakket is voltooid. U kunt de cache behouden naar een cachebestand (.caw).

Wanneer u de cache in een bestand ophoudt, wordt de cache sneller geladen. Dit verbetert de prestaties van de lookup-transformatie en het pakket. Houd er rekening mee dat wanneer u een cachebestand gebruikt, u werkt met gegevens die niet zo actueel zijn als de gegevens in de database.

Hier volgen extra voordelen van het behouden van de cache naar een bestand:

Hieronder ziet u de cacheopties:

  • De referentiegegevensset wordt gegenereerd met behulp van een tabel, weergave of SQL-query en geladen in de cache voordat de opzoektransformatie wordt uitgevoerd. U gebruikt ole DB-verbindingsbeheer voor toegang tot de gegevensset.

    Deze cacheoptie is compatibel met de volledige cacheoptie die beschikbaar is voor de lookup-transformatie in SQL Server 2005 Integration Services (SSIS).

  • De referentiegegevensset wordt gegenereerd op basis van een verbonden gegevensbron in de gegevensstroom of vanuit een cachebestand en wordt in de cache geladen voordat de opzoektransformatie wordt uitgevoerd. U gebruikt cacheverbindingsbeheer en optioneel de cachetransformatie voor toegang tot de gegevensset. Zie Cache Connection Manager en Cache Transform voor meer informatie.

  • De referentiegegevensset wordt gegenereerd met behulp van een tabel, weergave of SQL-query tijdens de uitvoering van de opzoektransformatie. De rijen met overeenkomende vermeldingen in de referentiegegevensset en de rijen zonder overeenkomende vermeldingen in de gegevensset worden in de cache geladen.

    Wanneer de geheugengrootte van de cache wordt overschreden, worden met de opzoektransformatie automatisch de minst gebruikte rijen uit de cache verwijderd.

    Deze cacheoptie is compatibel met de gedeeltelijke cacheoptie die beschikbaar is voor de lookup-transformatie in SQL Server 2005 Integration Services (SSIS).

  • De referentiegegevensset wordt gegenereerd met behulp van een tabel, weergave of SQL-query tijdens de uitvoering van de opzoektransformatie. Er worden geen gegevens in de cache opgeslagen.

    Deze cacheoptie is compatibel met de optie geen caching die beschikbaar is voor de lookup-transformatie in SQL Server 2005 Integration Services (SSIS).

Integration Services en SQL Server verschillen op de manier waarop ze tekenreeksen vergelijken. Als de opzoektransformatie is geconfigureerd voor het laden van de referentiegegevensset in de cache voordat de lookup-transformatie wordt uitgevoerd, voert Integration Services de vergelijking van zoekopdrachten uit in de cache. Anders maakt de opzoekbewerking gebruik van een geparameteriseerde SQL-instructie en voert SQL Server de opzoekvergelijking uit. Dit betekent dat de opzoektransformatie mogelijk een ander aantal overeenkomsten uit dezelfde opzoektabel retourneert, afhankelijk van het cachetype.

U kunt eigenschappen instellen via SSIS Designer of programmatisch. Zie de volgende onderwerpen voor meer informatie.

Verwante inhoud

Opzoektransformatie-editor (algemene pagina)

Gebruik de pagina Algemeen van het dialoogvenster Transformatieeditor opzoeken om de cachemodus te selecteren, het verbindingstype te selecteren en op te geven hoe rijen zonder overeenkomende vermeldingen moeten worden verwerkt.

Options

Opmerking

De overeenkomstuitvoer kan verschillen, afhankelijk van of de modus Volledige cache, Geen cachemodus of gedeeltelijke cachemodus wordt gebruikt:

  • In de modus Volledige cache wordt de database eenmaal opgevraagd tijdens de fase vóór uitvoering van de gegevensstroom en wordt de volledige referentieset in het geheugen opgehaald. Dit betekent dat we afhankelijk zijn van de overeenkomende regel van de SSIS-engine die hoofdlettergevoelig, gevoelig is met volgspaties en gevoelig met precisie en schaal van numerieke waarden.
  • Wanneer er echter geen cache of gedeeltelijke cachemodus wordt gebruikt, wordt er geen caching uitgevoerd tijdens de fase vooraf uitvoeren. Dit betekent dat we de overeenkomende regel van de brondatabase gebruiken. Als de bron bijvoorbeeld een SQL Server-database is en als kolomsortering niet hoofdlettergevoelig is, moet de overeenkomende regel ook niet hoofdlettergevoelig zijn.

Volledige cache
Genereer en laad de referentiegegevensset in de cache voordat de lookup-transformatie wordt uitgevoerd.

Gedeeltelijke cache
Genereer de referentiegegevensset tijdens de uitvoering van de lookup-transformatie. Laad de rijen met overeenkomende vermeldingen in de referentiegegevensset en de rijen zonder overeenkomende vermeldingen in de gegevensset in de cache.

Geen cache
Genereer de referentiegegevensset tijdens de uitvoering van de lookup-transformatie. Er worden geen gegevens in de cache geladen.

Cacheverbindingsbeheer
Configureer de opzoektransformatie om een cacheverbindingsbeheer te gebruiken. Deze optie is alleen beschikbaar als de optie Volledige cache is geselecteerd.

OLE DB-verbindingsbeheer
Configureer de opzoektransformatie om een OLE DB-verbindingsbeheer te gebruiken.

Opgeven hoe rijen zonder overeenkomende vermeldingen moeten worden verwerkt
Selecteer een optie voor het verwerken van rijen die niet overeenkomen met ten minste één vermelding in de referentiegegevensset.

Wanneer u Omleidingsrijen selecteert om geen overeenkomende uitvoer, worden de rijen omgeleid naar een uitvoer zonder overeenkomst en worden ze niet verwerkt als fouten. De optie Fout op de pagina Foutuitvoer van het dialoogvenster Transformatieeditor opzoeken is niet beschikbaar.

Wanneer u een andere optie selecteert in het vak Opgeven hoe u rijen verwerkt zonder overeenkomende invoerlijst , worden de rijen verwerkt als fouten. De optie Fout op de pagina Foutuitvoer is beschikbaar.

Externe resources

Blogbericht, cachemodi opzoeken op blogs.msdn.com

Opzoektransformatie-editor (verbindingspagina)

Gebruik de pagina Verbinding van het dialoogvenster Transformatieeditor opzoeken om een verbindingsbeheer te selecteren. Als u een OLE DB-verbindingsbeheer selecteert, selecteert u ook een query, tabel of weergave om de referentiegegevensset te genereren.

Options

De volgende opties zijn beschikbaar wanneer u volledige cache en cacheverbindingsbeheer selecteert op de pagina Algemeen van het dialoogvenster Transformatieeditor opzoeken .

Cacheverbindingsbeheer
Selecteer een bestaand cacheverbindingsbeheer in de lijst of maak een nieuwe verbinding door op Nieuw te klikken.

Nieuw
Maak een nieuwe verbinding met behulp van het dialoogvenster Cache Connection Manager Editor .

De volgende opties zijn beschikbaar wanneer u volledige cache, gedeeltelijke cache of geen cache selecteert en OLE DB-verbindingsbeheer op de pagina Algemeen van het dialoogvenster Transformatieeditor opzoeken .

OLE DB-verbindingsbeheer
Selecteer een bestaand OLE DB-verbindingsbeheer in de lijst of maak een nieuwe verbinding door op Nieuw te klikken.

Nieuw
Maak een nieuwe verbinding met behulp van het dialoogvenster OLE DB-verbindingsbeheer configureren .

Een tabel of weergave gebruiken
Selecteer een bestaande tabel of weergave in de lijst of maak een nieuwe tabel door op Nieuw te klikken.

Opmerking

Als u een SQL-instructie opgeeft op de pagina Geavanceerd van de Lookup Transformation Editor, overschrijft die SQL-instructie en vervangt u de tabelnaam die hier is geselecteerd.

Nieuw
Maak een nieuwe tabel met behulp van het dialoogvenster Tabel maken .

Resultaten van een SQL-query gebruiken
Kies deze optie om naar een bestaande query te bladeren, een nieuwe query te maken, de syntaxis van de query te controleren en een voorbeeld van queryresultaten te bekijken.

Buildquery
Maak de Transact-SQL instructie die moet worden uitgevoerd met behulp van Query Builder, een grafisch hulpprogramma dat wordt gebruikt om query's te maken door door gegevens te bladeren.

Bladeren
Gebruik deze optie om te bladeren naar een bestaande query die is opgeslagen als een bestand.

Query parseren
Controleer de syntaxis van de query.

Voorbeeld
Voorbeeld van resultaten bekijken met behulp van het dialoogvenster Voorbeeld van queryresultaten . Met deze optie worden maximaal 200 rijen weergegeven.

Externe resources

Blogbericht, cachemodi opzoeken op blogs.msdn.com

Opzoektransformatie-editor (pagina Kolommen)

Gebruik de pagina Kolommen van het dialoogvenster Transformatieeditor opzoeken om de join tussen de brontabel en de referentietabel op te geven en om opzoekkolommen in de referentietabel te selecteren.

Options

Beschikbare invoerkolommen
Bekijk de lijst met beschikbare invoerkolommen. De invoerkolommen zijn de kolommen in de gegevensstroom van een verbonden bron. De invoerkolommen en opzoekkolom moeten overeenkomende gegevenstypen hebben.

Gebruik een bewerking voor slepen en neerzetten om beschikbare invoerkolommen toe te wijzen aan opzoekkolommen.

U kunt ook invoerkolommen toewijzen aan opzoekkolommen met behulp van het toetsenbord door een kolom in de tabel Beschikbare invoerkolommen te markeren, op de toepassingstoets te drukken en vervolgens op Toewijzingen bewerken te klikken.

Beschikbare opzoekkolommen
De lijst met opzoekkolommen weergeven. De opzoekkolommen zijn kolommen in de referentietabel waarin u waarden wilt opzoeken die overeenkomen met de invoerkolommen.

Gebruik een bewerking voor slepen en neerzetten om beschikbare opzoekkolommen toe te wijzen aan invoerkolommen.

Gebruik de selectievakjes om opzoekkolommen in de referentietabel te selecteren waarop u opzoekbewerkingen wilt uitvoeren.

U kunt opzoekkolommen ook toewijzen aan invoerkolommen met behulp van het toetsenbord door een kolom in de tabel Beschikbare opzoekkolommen te markeren, op de toepassingstoets te drukken en vervolgens op Toewijzingen bewerken te klikken.

Opzoekkolom
De geselecteerde opzoekkolommen weergeven. De selecties worden weergegeven in de selectievakjes in de tabel Beschikbare opzoekkolommen .

Opzoekbewerking
Selecteer een opzoekbewerking in de lijst die u wilt uitvoeren op de opzoekkolom.

Uitvoeralias
Typ een alias voor de uitvoer voor elke opzoekkolom. De standaardwaarde is de naam van de opzoekkolom; U kunt echter elke unieke, beschrijvende naam selecteren.

Lookup Transformation Editor (geavanceerde pagina)

Gebruik de pagina Geavanceerd van het dialoogvenster Transformatieeditor opzoeken om gedeeltelijke caching te configureren en de SQL-instructie voor de opzoektransformatie te wijzigen.

Options

Cachegrootte (32-bits)
Pas de cachegrootte (in megabytes) aan voor 32-bits computers. De standaardwaarde is 5 megabytes.

Cachegrootte (64-bits)
Pas de cachegrootte (in megabytes) aan voor 64-bits computers. De standaardwaarde is 5 megabytes.

Cache inschakelen voor rijen zonder overeenkomende vermeldingen
Cacherijen zonder overeenkomende vermeldingen in de referentiegegevensset.

Toewijzing vanuit cache
Geef het percentage van de cache op dat moet worden toegewezen voor rijen zonder overeenkomende vermeldingen in de referentiegegevensset.

De SQL-instructie wijzigen
Wijzig de SQL-instructie die wordt gebruikt om de referentiegegevensset te genereren.

Opmerking

De optionele SQL-instructie die u op deze pagina opgeeft, overschrijft en vervangt de tabelnaam die u hebt opgegeven op de pagina Verbinding van de Lookup Transformation Editor. .

Parameters instellen
Wijs invoerkolommen toe aan parameters met behulp van het dialoogvenster Queryparameters instellen .

Externe resources

Blogbericht, cachemodi opzoeken op blogs.msdn.com

Zie ook

Fuzzy Lookup-transformatie
Termenzoektransformatie
Gegevensstroom
Integratieservicestransformaties

  • Last updated on