Delen via

SQL-taak uitvoeren

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

De SQL-taak Uitvoeren voert SQL-instructies of opgeslagen procedures uit vanuit een pakket. De taak kan één SQL-instructie of meerdere SQL-instructies bevatten die opeenvolgend worden uitgevoerd. U kunt de SQL-taak uitvoeren voor de volgende doeleinden:

  • Een tabel of weergave afkappen ter voorbereiding op het invoegen van gegevens.

  • Databaseobjecten zoals tabellen en weergaven maken, wijzigen en verwijderen.

  • Maak feiten- en dimensietabellen opnieuw voordat er gegevens in worden geladen.

  • Opgeslagen procedures uitvoeren. Als de SQL-instructie een opgeslagen procedure aanroept die resultaten van een tijdelijke tabel retourneert, gebruikt u de optie WITH RESULT SETS om metagegevens voor de resultatenset te definiëren.

  • Sla de rijenset op die wordt geretourneerd uit een query in een variabele.

De SQL-taak Uitvoeren kan worden gebruikt in combinatie met de Foreach-lus en For Loop-containers om meerdere SQL-instructies uit te voeren. Deze containers implementeren herhalende besturingsstromen in een pakket en ze kunnen de SQL-taak uitvoeren herhaaldelijk. Als u bijvoorbeeld de Foreach Loop-container gebruikt, kan een pakket bestanden in een map inventariseren en een SQL-taak uitvoeren om de SQL-instructie uit te voeren die in elk bestand is opgeslagen.

Verbinding maken met een gegevensbron

De SQL-taak Uitvoeren kan verschillende typen verbindingsbeheerders gebruiken om verbinding te maken met de gegevensbron waar de SQL-instructie of opgeslagen procedure wordt uitgevoerd. De taak kan de verbindingstypen gebruiken die worden vermeld in de volgende tabel.

Verbindingstype Verbindingsbeheer
Excel Excel Connection Manager
OLE DB OLE DB-verbindingsbeheer
ODBC ODBC-verbindingsbeheer
LAWAAI ADO-verbindingsbeheer
ADO.NET ADO.NET Verbindingsbeheer
SQLMOBILE Verbindingsbeheer voor SQL Server Compact Edition

SQL-instructies maken

De bron van de SQL-instructies die door deze taak worden gebruikt, kan een taakeigenschap zijn die een instructie bevat, een verbinding met een bestand dat een of meer instructies bevat of de naam van een variabele die een instructie bevat. De SQL-instructies moeten worden geschreven in het dialect van het brondatabasebeheersysteem (DBMS). Zie Integration Services (SSIS)-query's voor meer informatie.

Als de SQL-instructies worden opgeslagen in een bestand, gebruikt de taak een bestandsverbindingsbeheer om verbinding te maken met het bestand. Zie Bestandsverbindingsbeheervoor meer informatie.

In SSIS Designer kunt u het dialoogvenster SQL-taakeditor uitvoeren gebruiken om SQL-instructies te typen of Query Builder te gebruiken, een grafische gebruikersinterface voor het maken van SQL-query's.

Opmerking

Geldige SQL-instructies die buiten de taak SQL Uitvoeren zijn geschreven, worden mogelijk niet succesvol geparseerd door de taak SQL Uitvoeren.

Opmerking

De execute SQL-taak maakt gebruik van de opsommingswaarde RecognizeAll ParseMode. Zie ManagedBatchParser-naamruimte voor meer informatie.

Meerdere instructies in een batch verzenden

Als u meerdere instructies in een SQL-taak uitvoeren opneemt, kunt u deze groeperen en uitvoeren als een batch. Als u het einde van een batch wilt signaleren, gebruikt u de GO-opdracht. Alle SQL-instructies tussen twee GO-opdrachten worden in een batch verzonden naar de OLE DB-provider die moet worden uitgevoerd. De SQL-opdracht kan meerdere batches bevatten, gescheiden door GO-opdrachten.

Er gelden beperkingen voor de soorten SQL-instructies die u in een batch kunt groeperen. Zie Batches of Statements voor meer informatie.

Als de SQL-taak Uitvoeren een batch MET SQL-instructies uitvoert, zijn de volgende regels van toepassing op de batch:

  • Slechts één instructie kan een resultatenset retourneren en dit moet de eerste instructie in de batch zijn.

  • Als de resultatenset resultaatbindingen gebruikt, moeten de query's hetzelfde aantal kolommen retourneren. Als de query's een ander aantal kolommen retourneren, mislukt de taak. Zelfs als de taak mislukt, kunnen de query's die worden uitgevoerd, zoals DELETE- of INSERT-query's, slagen.

  • Als de resultaatbindingen kolomnamen gebruiken, moet de query kolommen retourneren met dezelfde namen als de namen van de resultatensets die in de taak worden gebruikt. Als de kolommen ontbreken, mislukt de taak.

  • Als de taak parameterbinding gebruikt, moeten alle query's in de batch hetzelfde aantal en dezelfde typen parameters hebben.

Geparameteriseerde SQL-opdrachten uitvoeren

SQL-instructies en opgeslagen procedures gebruiken vaak invoerparameters, uitvoerparameters en retourcodes. De sql-taak Uitvoeren ondersteunt de parametertypen Invoer, Uitvoer en ReturnValue . U gebruikt het invoertype voor invoerparameters, Uitvoer voor uitvoerparameters en ReturnValue voor retourcodes.

Opmerking

U kunt parameters in een SQL-taak uitvoeren alleen gebruiken als de gegevensprovider deze ondersteunt.

Een resultaatsettype opgeven

Afhankelijk van het type SQL-opdracht wordt een resultatenset al dan niet geretourneerd naar de SQL-taak uitvoeren. Een SELECT-instructie retourneert bijvoorbeeld meestal een resultatenset, maar een INSERT-instructie niet. De resultatenset van een SELECT-instructie kan nul rijen, één rij of veel rijen bevatten. Opgeslagen procedures kunnen ook een geheel getal, een retourcode genoemd, retourneren die de uitvoeringsstatus van de procedure aangeeft. In dat geval bestaat de resultatenset uit één rij.

De SQL-taak uitvoeren configureren

U kunt de SQL-taak uitvoeren op de volgende manieren configureren:

  • Geef het type verbindingsbeheer op dat moet worden gebruikt om verbinding te maken met een database.

  • Geef het type resultatenset op dat door de SQL-instructie wordt geretourneerd.

  • Geef een time-out op voor de SQL-instructies.

  • Geef de bron van de SQL-instructie op.

  • Geef aan of de taak de voorbereidingsfase voor de SQL-instructie overslaat.

  • Als u het ADO-verbindingstype gebruikt, moet u aangeven of de SQL-instructie een opgeslagen procedure is. Voor andere verbindingstypen is deze eigenschap alleen-lezen en is de waarde ervan altijd false.

U kunt eigenschappen programmatisch of via SSIS Designer instellen.

Algemene pagina - SQL-taakeditor uitvoeren

Gebruik de pagina Algemeen van het dialoogvenster SQL-taakeditor uitvoeren om de SQL-taak uitvoeren te configureren en de SQL-instructie op te geven die door de taak wordt uitgevoerd.

Zie Transact-SQL Reference (Database Engine) voor meer informatie over de Transact-SQL querytaal.

Statische opties

Naam
Geef een unieke naam op voor de SQL-taak uitvoeren in de werkstroom. De opgegeven naam wordt weergegeven in SSIS Designer.

Beschrijving
Beschrijf de SQL-taak uitvoeren. Om pakketten zelfdocumenterend te maken en gemakkelijker te onderhouden, beschrijft u de taak in termen van het doel ervan.

Timeout
Geef het maximum aantal seconden op dat de taak wordt uitgevoerd voordat er een time-out optreedt. Een waarde van 0 geeft een oneindige tijd aan. De standaardwaarde is 0.

Opmerking

Er treedt geen time-out op bij opgeslagen procedures als ze slaapfunctionaliteit emuleren door voldoende tijd te bieden voor het tot stand brengen van verbindingen en het voltooien van transacties die langer is dan het aantal seconden dat is opgegeven door TimeOut. Opgeslagen procedures waarmee query's worden uitgevoerd, zijn echter altijd onderhevig aan de tijdbeperking die is opgegeven door TimeOut.

CodePage
Geef de codepagina op die moet worden gebruikt bij het vertalen van Unicode-waarden in variabelen. De standaardwaarde is de codepagina van de lokale computer.

Opmerking

Wanneer de SQL-taak Uitvoeren een ADO- of ODBC-verbindingsbeheer gebruikt, is de eigenschap CodePage niet beschikbaar. Als voor uw oplossing het gebruik van een codepagina is vereist, gebruikt u een OLE DB of een ADO.NET verbindingsbeheer met de SQL-taak uitvoeren.

TypeConversionMode
Wanneer u deze eigenschap instelt op Toegestaan, probeert de SQL-taak uitvoeren uitvoerparameter en queryresultaten te converteren naar het gegevenstype van de variabele waaraan de resultaten zijn toegewezen. Dit is van toepassing op het resultaatsettype enkele rij .

ResultSet
Geef het resultaattype op dat wordt verwacht door de SQL-instructie die wordt uitgevoerd. Kies uit één rij, volledige resultatenset, XML of Geen.

Verbindingstype
Kies het type verbindingsbeheer dat u wilt gebruiken om verbinding te maken met de gegevensbron. Beschikbare verbindingstypen zijn OLE DB, ODBC, ADO, ADO.NET en SQLMOBILE.

Verwante onderwerpen:OLE DB Connection Manager, ODBC Connection Manager, ADO Connection Manager, ADO.NET Connection Manager, SQL Server Compact Edition Connection Manager

Verbinding
Kies de verbinding in een lijst met gedefinieerde verbindingsbeheerders. Als u een nieuwe verbinding wilt maken, selecteert u <Nieuwe verbinding...>.

SQLSourceType
Selecteer het brontype van de SQL-instructie die door de taak wordt uitgevoerd.

Afhankelijk van het verbindingsbeheertype dat SQL-taak uitvoert, moet u specifieke parametermarkeringen gebruiken in geparameteriseerde SQL-instructies.

Deze eigenschap bevat de opties in de volgende tabel.

Waarde Description
Directe invoer Stel de bron in op een Transact-SQL instructie. Als u deze waarde selecteert, wordt de dynamische optie SQLStatement weergegeven.
Bestandsverbinding Selecteer een bestand met een Transact-SQL instructie. Als u deze optie instelt, wordt de dynamische optie FileConnection weergegeven.
Veranderlijk Stel de bron in op een variabele waarmee de instructie Transact-SQL wordt gedefinieerd. Als u deze waarde selecteert, wordt de dynamische optie SourceVariable weergegeven.

QueryIsStoredProcedure
Geeft aan of de opgegeven SQL-instructie die moet worden uitgevoerd een opgeslagen procedure is. Deze eigenschap is lees/schrijfbaar alleen als de taak gebruikmaakt van de ADO-verbindingsbeheerder. Anders is de eigenschap alleen-lezen en de waarde is onwaar.

BypassPrepare
Geef aan of de SQL-instructie is voorbereid. true slaat het voorbereiden over; false bereidt de SQL-instructie voor voordat deze wordt uitgevoerd. Deze optie is alleen beschikbaar voor OLE DB-verbindingen die ondersteuning bieden voor voorbereiding.

Verwante onderwerpen:Voorbereide uitvoering

navigeren
Zoek een bestand met een SQL-instructie met behulp van het dialoogvenster Openen . Selecteer een bestand om de inhoud van het bestand als een SQL-instructie te kopiëren naar de eigenschap SQLStatement .

BuildQuery
Maak een SQL-instructie met behulp van het dialoogvenster Opbouwfunctie voor query's , een grafisch hulpprogramma dat wordt gebruikt om query's te maken. Deze optie is beschikbaar wanneer de optie SQLSourceType is ingesteld op Directe invoer.

Query parseren
Valideer de syntaxis van de SQL-instructie.

Dynamische opties voor SQLSourceType

SQLSourceType = Directe invoer

SQLStatement
Typ de SQL-instructie die u wilt uitvoeren in het keuzevak of klik op de bladerknop (...) om de SQL-instructie in het dialoogvenster SQL-query invoeren te typen of klik op Query samenstellen om de instructie op te stellen met het dialoogvenster Opbouwfunctie voor query's .

Verwante onderwerpen:Opbouwfunctie voor query's

SQLSourceType = Bestandsverbinding

FileConnection
Selecteer een bestaand bestandsverbindingsbeheer of klik op <Nieuwe verbinding...> om een nieuw verbindingsbeheer te maken.

Verwante onderwerpen:Bestandsverbindingsbeheer, Editor voor Bestandsverbindingsbeheer

SQLSourceType = Variable

SourceVariable
Selecteer een bestaande variabele of klik op <Nieuwe variabele...> om een nieuwe variabele te maken.

Verwante onderwerpen:SSIS-variabelen (Integration Services),variabele toevoegen

Pagina parametertoewijzing - SQL-taakeditor uitvoeren

Gebruik de pagina Parametertoewijzing van het dialoogvenster SQL-taakeditor uitvoeren om variabelen toe te wijzen aan parameters in de SQL-instructie.

Options

Variabelenaam
Nadat u een parametertoewijzing hebt toegevoegd door op Toevoegen te klikken, selecteert u een systeem- of door de gebruiker gedefinieerde variabele in de lijst of klikt u op <Nieuwe variabele...> om een nieuwe variabele toe te voegen met behulp van het dialoogvenster Variabele toevoegen .

Verwante onderwerpen:SSIS-variabelen (Integration Services)

Direction
Selecteer de richting van de parameter. Wijs elke variabele toe aan een invoerparameter, uitvoerparameter of een retourcode.

Gegevenstype
Selecteer het gegevenstype van de parameter. De lijst met beschikbare gegevenstypen is specifiek voor de provider die is geselecteerd in de verbindingsbeheerder die door de taak wordt gebruikt.

Parameternaam
Geef een parameternaam op.

Afhankelijk van het verbindingsbeheertype dat door de taak wordt gebruikt, moet u getallen of parameternamen gebruiken. Voor sommige verbindingsbeheertypen moet het eerste teken van de parameternaam het @-teken zijn, specifieke namen zoals @Param1 of kolomnamen als parameternamen.

Parametergrootte
Geef de grootte op van parameters met een variabele lengte, zoals tekenreeksen en binaire velden.

Deze instelling zorgt ervoor dat de provider voldoende ruimte toewijst voor parameterwaarden voor variabele lengte.

Add
Klik hier om een parametertoewijzing toe te voegen.

Remove
Selecteer een parametertoewijzing in de lijst en klik vervolgens op Verwijderen.

Resultaatsetpagina - SQL-taakeditor uitvoeren

Gebruik de pagina Resultatenset van het dialoogvenster SQL-taakeditor uitvoeren om het resultaat van de SQL-instructie toe te wijzen aan nieuwe of bestaande variabelen. De opties in dit dialoogvenster zijn uitgeschakeld als ResultSet op de pagina Algemeen is ingesteld op Geen.

Options

Resultaatnaam
Door op Toevoegen te klikken nadat u een toewijzingsset voor resultatensets hebt toegevoegd, geeft u een naam op voor het resultaat. Afhankelijk van het type resultatenset, moet u specifieke resultaatnamen gebruiken.

Als het type resultatenset één rij is, kunt u de naam van een kolom gebruiken die wordt geretourneerd door de query of het getal dat de positie van een kolom in de kolomlijst aangeeft van een kolom die door de query wordt geretourneerd.

Als het resultaatsettype Volledige resultatenset of XML is, moet u 0 gebruiken als de naam van de resultatenset.

Variabelenaam
Wijs de resultatenset toe aan een variabele door een variabele te selecteren of op < te klikken... > om een nieuwe variabele toe te voegen met behulp van het dialoogvenster Variabele toevoegen.

Add
Klik om een resultaatset-mapping toe te voegen.

Remove
Selecteer in de lijst een resultatensettoewijzing en klik vervolgens op Verwijderen.

Parameters in de SQL-uitvoeringstaak

SQL-instructies en opgeslagen procedures gebruiken vaak invoerparameters , uitvoerparameters en retourcodes. In Integration Services ondersteunt de sql-taak Uitvoeren de parametertypen Input, Output en ReturnValue . U gebruikt het invoertype voor invoerparameters, Uitvoer voor uitvoerparameters en ReturnValue voor retourcodes.

Opmerking

U kunt parameters in een SQL-taak uitvoeren alleen gebruiken als de gegevensprovider deze ondersteunt.

Parameters in SQL-opdrachten, waaronder query's en opgeslagen procedures, worden toegewezen aan door de gebruiker gedefinieerde variabelen die zijn gemaakt binnen het bereik van de SQL-taak Uitvoeren, een bovenliggende container of binnen het bereik van het pakket. De waarden van variabelen kunnen tijdens de ontwerptijd worden ingesteld of dynamisch worden ingevuld tijdens runtime. U kunt ook parameters toewijzen aan systeemvariabelen. Zie SSIS-variabelen (Integration Services) en systeemvariabelen voor meer informatie.

Het werken met parameters en retourcodes in een EXECUTE SQL-taak is echter meer dan alleen weten welke parametertypen de taak ondersteunt en hoe deze parameters worden toegewezen. Er zijn aanvullende gebruiksvereisten en richtlijnen voor het succesvol gebruik van parameters en retourcodes bij het uitvoeren van de SQL-taak. In de rest van dit onderwerp worden de volgende gebruiksvereisten en richtlijnen behandeld:

Parameternamen en markeringen

Afhankelijk van het verbindingstype dat de SQL-taak uitvoeren gebruikt, gebruikt de syntaxis van de SQL-opdracht verschillende parametermarkeringen. Voor het ADO.NET verbindingsbeheertype is bijvoorbeeld vereist dat de SQL-opdracht een parametermarkering gebruikt in de indeling @varParameter, terwijl voor het OLE DB-verbindingstype de parametermarkering (?) is vereist.

De namen die u als parameternamen in de toewijzingen tussen variabelen en parameters kunt gebruiken, verschillen ook per verbindingsbeheertype. Het type ADO.NET verbindingsbeheer gebruikt bijvoorbeeld een door de gebruiker gedefinieerde naam met een @-voorvoegsel, terwijl het type OLE DB-verbindingsbeheer vereist dat u de numerieke waarde van een op 0 gebaseerde rangtelwoord als parameternaam gebruikt.

De volgende tabel bevat een overzicht van de vereisten voor SQL-opdrachten voor de verbindingsbeheertypen die de SQL-taak uitvoeren kan gebruiken.

Verbindingstype Parameter markeerder Parameternaam Voorbeeld van SQL-opdracht
LAWAAI ? Param1, Param2, ... SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?
ADO.NET @<parameternaam> @<parameternaam> SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = @parmContactID
ODBC ? 1, 2, 3, ... SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?
EXCEL en OLE DB ? 0, 1, 2, 3, ... SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?

Parameters gebruiken met ADO.NET en ADO-verbindingsmanagers

ADO.NET- en ADO-verbindingsbeheerders hebben specifieke vereisten voor SQL-opdrachten die gebruikmaken van parameters:

  • ADO.NET verbindingsbeheerders vereisen dat de SQL-opdracht parameternamen gebruikt als parametermarkeringen. Dit betekent dat variabelen rechtstreeks aan parameters kunnen worden toegewezen. De variabele @varName wordt bijvoorbeeld toegewezen aan de benoemde @parName parameter en geeft een waarde aan de parameter @parName.

  • ADO-verbindingsbeheerders vereisen dat de SQL-opdracht vraagtekens (?) als parametermarkeringen gebruikt. U kunt echter elke door de gebruiker gedefinieerde naam gebruiken, met uitzondering van gehele getallen, als parameternamen.

Als u waarden wilt opgeven voor parameters, worden variabelen toegewezen aan parameternamen. Vervolgens gebruikt de Taak SQL uitvoeren de ordinale waarde van de parameternaam in de lijst met parameters om waarden van variabelen naar parameters te laden.

Parameters gebruiken met EXCEL-, ODBC- en OLE DB-verbindingsbeheer

Excel-, ODBC- en OLE DB-verbindingsbeheerders vereisen dat de SQL-opdracht vraagtekens (?) gebruikt als parametermarkeringen en numerieke waarden op basis van 0 of 1 als parameternamen. Als de SQL-taak Uitvoeren gebruikmaakt van het ODBC-verbindingsbeheer, krijgt de parameternaam die is toegewezen aan de eerste parameter in de query de naam 1; anders heeft de parameter de naam 0. Voor volgende parameters geeft de numerieke waarde van de parameternaam de parameter aan in de SQL-opdracht waaraan de parameternaam is toegewezen. De parameter met de naam 3 wordt bijvoorbeeld toegewezen aan de derde parameter, die wordt vertegenwoordigd door het derde vraagteken (?) in de SQL-opdracht.

Als u waarden wilt opgeven voor parameters, worden variabelen toegewezen aan parameternamen en gebruikt de SQL-taak uitvoeren de rangwaarde van de parameternaam om waarden van variabelen naar parameters te laden.

Afhankelijk van de provider die de verbindingsbeheerder gebruikt, worden sommige OLE DB-gegevenstypen mogelijk niet ondersteund. Het Excel-stuurprogramma herkent bijvoorbeeld slechts een beperkte set gegevenstypen. Zie Excel Source voor meer informatie over het gedrag van de Jet-provider met het Excel-stuurprogramma.

Parameters gebruiken met OLE DB-verbindingsmanagers

Wanneer de SQL-taak uitvoeren gebruikmaakt van het OLE DB-verbindingsbeheer, is de eigenschap BypassPrepare van de taak beschikbaar. U moet deze eigenschap instellen op Waar als de SQL-taak Uitvoeren SQL-instructies met parameters gebruikt.

Wanneer u een OLE DB-verbindingsbeheer gebruikt, kunt u geen geparameteriseerde subquery's gebruiken omdat de SQL-taak uitvoeren geen parametergegevens kan afleiden via de OLE DB-provider. U kunt echter een expressie gebruiken om de parameterwaarden samen te voegen in de querytekenreeks en om de eigenschap SqlStatementSource van de taak in te stellen.

Parameters gebruiken met datum- en tijdgegevenstypen

Datum- en tijdparameters gebruiken met ADO.NET en ADO-verbindingsmanagers

Bij het lezen van gegevens van de SQL Server-typen, tijd en datum/tijd-offset heeft een SQL-taak uitvoeren die gebruikmaakt van een ADO.NET of ADO-verbindingsbeheer de volgende aanvullende vereisten:

  • Voor tijdgegevens moet voor een ADO.NET verbindingsbeheer deze gegevens worden opgeslagen in een parameter waarvan het parametertype Invoer of Uitvoer is en waarvan het gegevenstype tekenreeks is.

  • Voor datetimeoffset-gegevens moet voor een ADO.NET verbindingsbeheer deze gegevens worden opgeslagen in een van de volgende parameters:

    • Een parameter waarvan het parametertype Invoer is en waarvan het gegevenstype tekenreeks is.

    • Een parameter waarvan het parametertype Output of ReturnValue is en waarvan het gegevenstype datetimeoffset, tekenreeks of datum/tijd2 is. Als u een parameter selecteert waarvan het gegevenstype tekenreeks of datum/tijd2 is, converteert Integration Services de gegevens naar tekenreeks of datum/tijd2.

  • Een ADO-verbindingsbeheerder vereist dat tijd - of datetimeoffset-gegevens worden opgeslagen in een parameter waarvan het parametertype Input of Output is en waarvan het gegevenstype adVarWchar is.

Zie Gegevenstypen (Transact-SQL) en Integration Services-gegevenstypen voor meer informatie over SQL Server-gegevenstypen en hoe deze worden toegewezen aan Integration Services-gegevenstypen.

Datum- en tijdparameters gebruiken met OLE DB-verbindingsmanagers

Wanneer u een OLE DB-verbindingsbeheer gebruikt, heeft een SQL-taak uitvoeren specifieke opslagvereisten voor gegevens van de SQL Server-gegevenstypen, datum, tijd, datum/tijd, datum/tijd2 en datetimeoffset. U moet deze gegevens opslaan in een van de volgende parametertypen:

  • Een invoerparameter van het gegevenstype NVARCHAR.

  • Een uitvoerparameter van het juiste gegevenstype, zoals vermeld in de volgende tabel.

    ** Parameter van het type uitvoer Datumgegevenstype
    DBDATE date
    DBTIME2 time
    DB-tijdstempel datum/tijd, datum/tijd2
    DBTIMESTAMPOFFSET datetimeoffset

Als de gegevens niet zijn opgeslagen in de juiste invoer- of uitvoerparameter, mislukt het pakket.

Datum- en tijdparameters gebruiken met ODBC-verbindingsmanagers

Wanneer u een ODBC-verbindingsbeheer gebruikt, heeft een Execute SQL-taak specifieke opslagvereisten voor gegevens met een van de SQL Server-gegevenstypen, datum, tijd, datetime, datetime2 of datetimeoffset. U moet deze gegevens opslaan in een van de volgende parametertypen:

  • Een invoerparameter van het gegevenstype SQL_WVARCHAR

  • Een uitvoerparameter met het juiste gegevenstype, zoals vermeld in de volgende tabel.

    Uitvoerparameter type Datumgegevenstype
    SQL_DATE date
    SQL_SS_TIME2 time
    SQL_TYPE_TIMESTAMP

    – of –

    SQL_TIMESTAMP    		 datum/tijd, datum/tijd2
    SQL_SS_TIMESTAMPOFFSET datetimeoffset

Als de gegevens niet zijn opgeslagen in de juiste invoer- of uitvoerparameter, mislukt het pakket.

Parameters gebruiken in WHERE-clausules

SELECT-, INSERT-, UPDATE- en DELETE-opdrachten bevatten vaak WHERE-componenten om filters op te geven die de voorwaarden definiëren waaraan elke rij in de brontabellen moet voldoen om in aanmerking te komen voor een SQL-opdracht. Parameters bieden de filterwaarden in de WHERE-clausules.

U kunt parametermarkeringen gebruiken om dynamisch parameterwaarden op te geven. De regels waarvoor parametermarkeringen en parameternamen kunnen worden gebruikt in de SQL-instructie, zijn afhankelijk van het type verbindingsbeheer dat door de execute SQL wordt gebruikt.

De volgende tabel bevat voorbeelden van de SELECT-opdracht op verbindingsbeheertype. De instructies INSERT, UPDATE en DELETE zijn vergelijkbaar. In de voorbeelden wordt SELECT gebruikt om producten te retourneren uit de tabel Product met AdventureWorks2025 een Product-id die groter is dan en kleiner is dan de waarden die zijn opgegeven door twee parameters.

Verbindingstype SELECT-syntaxis
EXCEL, ODBC en OLEDB SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ?
LAWAAI SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ?
ADO.NET SELECT* FROM Production.Product WHERE ProductId > @parmMinProductID AND ProductID < @parmMaxProductID

Voor de voorbeelden zijn parameters met de volgende namen vereist:

  • De VERBINDINGSbeheerders van EXCEL en OLED DB gebruiken de parameternamen 0 en 1. Het ODBC-verbindingstype maakt gebruik van 1 en 2.

  • Het ADO-verbindingstype kan gebruikmaken van twee parameternamen, zoals Param1 en Param2, maar de parameters moeten worden toegewezen door hun rangtelpositie in de parameterlijst.

  • Het ADO.NET verbindingstype gebruikt de parameternamen @parmMinProductID en @parmMaxProductID.

Parameters gebruiken met opgeslagen procedures

SQL-opdrachten die opgeslagen procedures uitvoeren, kunnen ook parametertoewijzing gebruiken. De regels voor het gebruik van parametermarkeringen en parameternamen zijn afhankelijk van het type verbindingsbeheer dat door sql uitvoeren wordt gebruikt, net zoals de regels voor geparameteriseerde query's.

De volgende tabel bevat voorbeelden van de EXEC-opdracht op verbindingsbeheertype. In de voorbeelden wordt de opgeslagen procedure uspGetBillOfMaterials uitgevoerd in AdventureWorks2025. De opgeslagen procedure maakt gebruik van de @StartProductID en @CheckDateinvoerparameters .

Verbindingstype EXEC-syntaxis
EXCEL en OLEDB EXEC uspGetBillOfMaterials ?, ?
ODBC {call uspGetBillOfMaterials(?, ?)}

Zie het onderwerp Procedureparameters in de Naslaginformatie voor ODBC-programmeurs in de MSDN-bibliotheek voor meer informatie over ODBC-aanroepsyntaxis.
LAWAAI Als IsQueryStoredProcedure is ingesteld op False, EXEC uspGetBillOfMaterials ?, ?

Als IsQueryStoredProcedure is ingesteld op True, uspGetBillOfMaterials
ADO.NET Als IsQueryStoredProcedure ingesteld is op False, EXEC uspGetBillOfMaterials @StartProductID, @CheckDate

Als de eigenschap IsQueryStoredProcedure is ingesteld op True, uspGetBillOfMaterials

Voor het gebruik van uitvoerparameters vereist de syntaxis dat het trefwoord OUTPUT elke parametermarkering volgt. De volgende syntaxis van de uitvoerparameter is bijvoorbeeld juist: EXEC myStoredProcedure ? OUTPUT.

Zie EXECUTE (Transact-SQL) voor meer informatie over het gebruik van invoer- en uitvoerparameters met Transact-SQL opgeslagen procedures.

Query-parameters toewijzen aan variabelen

In deze sectie wordt beschreven hoe u een geparameteriseerde SQL-instructie gebruikt in de SQL-taak UITVOEREN en toewijzingen maakt tussen variabelen en de parameters in de SQL-instructie.

  1. Open in SQL Server Data Tools (SSDT) het Integration Services-pakket waarmee u wilt werken.

  2. Dubbelklik in Solution Explorer op het pakket om het te openen.

  3. Klik op het tabblad Controlestroom .

  4. Als het pakket nog geen SQL-taak uitvoeren bevat, voegt u er een toe aan de controlestroom van het pakket. Zie Een taak of een container toevoegen of verwijderen in een besturingsstroom voor meer informatie.

  5. Dubbelklik op de SQL-taak Uitvoeren.

  6. Geef een geparameteriseerde SQL-opdracht op een van de volgende manieren op:

    • Gebruik directe invoer en typ de SQL-opdracht in de eigenschap SQLStatement.

    • Gebruik directe invoer, klik op Query bouwen en maak vervolgens een SQL-opdracht met behulp van de grafische hulpprogramma's die de opbouwfunctie voor query's biedt.

    • Gebruik een bestandsverbinding en verwijs vervolgens naar het bestand dat de SQL-opdracht bevat.

    • Gebruik een variabele en verwijs vervolgens naar de variabele die de SQL-opdracht bevat.

    De parametermarkeringen die u in geparameteriseerde SQL-instructies gebruikt, zijn afhankelijk van het verbindingstype dat de SQL-taak uitvoeren gebruikt.

    Verbindingstype Parametermarkering
    LAWAAI ?
    ADO.NET en SQLMOBILE @<parameternaam>
    ODBC ?
    EXCEL en OLE DB ?

    De volgende tabel bevat voorbeelden van de SELECT-opdracht op verbindingsbeheertype. Parameters verstrekken de filterwaarden in de WHERE-clausules. In de voorbeelden wordt SELECT gebruikt om producten te retourneren uit de tabel Product met AdventureWorks2025 een Product-id die groter is dan en kleiner is dan de waarden die zijn opgegeven door twee parameters.

    Verbindingstype SELECT-syntaxis
    EXCEL, ODBC en OLEDB SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ?
    LAWAAI SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ?
    ADO.NET SELECT* FROM Production.Product WHERE ProductId > @parmMinProductID AND ProductID < @parmMaxProductID

  7. Klik op Parametertoewijzing.

  8. Als u een parametertoewijzing wilt toevoegen, klikt u op Toevoegen.

  9. Geef een naam op in het vak Parameternaam .

    De parameternamen die u gebruikt, zijn afhankelijk van het verbindingstype dat de SQL-taak uitvoeren gebruikt.

    Verbindingstype Parameternaam
    LAWAAI Param1, Param2, ...
    ADO.NET en SQLMOBILE @<parameternaam>
    ODBC 1, 2, 3, ...
    EXCEL en OLE DB 0, 1, 2, 3, ...

  10. Selecteer een variabele in de lijst Variabelenaam . Zie voor meer informatie Toevoegen, Verwijderen, Wijzig Bereik van gebruiker gedefinieerde variabele in een pakket.

  11. Geef in de lijst Direction op of de parameter een invoer, een uitvoer of een retourwaarde is.

  12. Stel in de lijst Gegevenstype het gegevenstype van de parameter in.

    Belangrijk

    Het gegevenstype van de parameter moet compatibel zijn met het gegevenstype van de variabele.

  13. Herhaal stap 8 tot en met 11 voor elke parameter in de SQL-instructie.

    Belangrijk

    De volgorde van parametertoewijzingen moet hetzelfde zijn als de volgorde waarin de parameters worden weergegeven in de SQL-instructie.

  14. Klik op OK.

De waarden van retourcodes ophalen

Een opgeslagen procedure kan een geheel getal, een retourcode genoemd, retourneren om de uitvoeringsstatus van een procedure aan te geven. Als u retourcodes wilt implementeren in de SQL-taak Uitvoeren, gebruikt u parameters van het type ReturnValue .

De volgende tabel bevat een overzicht van verbindingstype enkele voorbeelden van EXEC-opdrachten die retourcodes implementeren. In alle voorbeelden wordt een invoerparameter gebruikt. De regels voor het gebruik van parametermarkeringen en parameternamen zijn hetzelfde voor alle parametertypen Input, Output en ReturnValue.

Sommige syntaxis biedt geen ondersteuning voor letterlijke parameters. In dat geval moet u de parameterwaarde opgeven met behulp van een variabele.

Verbindingstype EXEC-syntaxis
EXCEL en OLEDB EXEC ? = myStoredProcedure 1
ODBC {? = call myStoredProcedure(1)}

Zie het onderwerp Procedureparameters in de Naslaginformatie voor ODBC-programmeurs in de MSDN-bibliotheek voor meer informatie over ODBC-aanroepsyntaxis.
LAWAAI Als IsQueryStoreProcedure is ingesteld op False, EXEC ? = myStoredProcedure 1

Als IsQueryStoreProcedure is ingesteld op True, myStoredProcedure
ADO.NET Set IsQueryStoreProcedure is ingesteld op True.

myStoredProcedure

In de syntaxis die in de vorige tabel wordt weergegeven, gebruikt de SQL-taak Uitvoeren het type directe invoerbron om de opgeslagen procedure uit te voeren. De SQL-taak Uitvoeren kan ook het brontype Bestandsverbinding gebruiken om een opgeslagen procedure uit te voeren. Ongeacht of de SQL-taak uitvoeren gebruikmaakt van het brontype Directe invoer of Bestandsverbinding , gebruikt u een parameter van het type ReturnValue om de retourcode te implementeren.

Zie RETURN (Transact-SQL)voor meer informatie over het gebruik van retourcodes met Transact-SQL opgeslagen procedures.

Resultaatsets in de SQL-opdracht uitvoeren

Of in een Integration Services-pakket een resultatenset wordt geretourneerd naar de SQL-taak Uitvoeren, is afhankelijk van het type SQL-opdracht dat door de taak wordt gebruikt. Een SELECT-instructie retourneert bijvoorbeeld meestal een resultatenset, maar een INSERT-instructie niet.

Wat de resultatenset bevat, verschilt ook per SQL-opdracht. De resultatenset van een SELECT-instructie kan bijvoorbeeld nul rijen, één rij of veel rijen bevatten. De resultatenset van een SELECT-instructie die een telling retourneert of een som bevat echter slechts één rij.

Werken met resultatensets in een SQL-taak uitvoeren is meer dan alleen weten of de SQL-opdracht een resultatenset retourneert en wat die resultatenset bevat. Er zijn aanvullende gebruiksvereisten en richtlijnen voor het succesvol gebruik van resultatensets in de Execute SQL-taak. In de rest van dit onderwerp worden de volgende gebruiksvereisten en richtlijnen behandeld:

Een resultaatsettype opgeven

De SQL-taak Uitvoeren ondersteunt de volgende typen resultatensets:

  • De resultatenset None wordt gebruikt wanneer de query geen resultaten retourneert. Deze resultatenset wordt bijvoorbeeld gebruikt voor query's waarmee records in een tabel worden toegevoegd, gewijzigd en verwijderd.

  • De resultatenset met één rij wordt gebruikt wanneer de query slechts één rij retourneert. Deze resultatenset wordt bijvoorbeeld gebruikt voor een SELECT-instructie die een telling of een som retourneert.

  • De resultatenset Volledige resultatenset wordt gebruikt wanneer de query meerdere rijen retourneert. Deze resultatenset wordt bijvoorbeeld gebruikt voor een SELECT-instructie waarmee alle rijen in een tabel worden opgehaald.

  • De XML-resultatenset wordt gebruikt wanneer de query een resultatenset retourneert in een XML-indeling. Deze resultatenset wordt bijvoorbeeld gebruikt voor een SELECT-instructie die een FOR XML-component bevat.

Als de execute SQL-taak gebruikmaakt van de resultatenset Volledige resultatenset en de query meerdere rijensets retourneert, retourneert de taak alleen de eerste rijset. Als deze rijset een fout genereert, rapporteert de taak de fout. Als andere rijensets fouten genereren, worden deze niet door de taak gerapporteerd.

Een variabele vullen met een resultatenset

U kunt de resultatenset binden die een query retourneert aan een door de gebruiker gedefinieerde variabele, als het resultaatsettype één rij, een rijset of XML is.

Als het resultaatsettype Één rij is, kunt u een kolom in het resultaat van het resultaat binden aan een variabele met behulp van de kolomnaam als de naam van de resultatenset, of u kunt de rangtelpositie van de kolom in de kolomlijst gebruiken als de naam van de resultatenset. De naam van de resultatenset voor de query SELECT Color FROM Production.Product WHERE ProductID = ? kan bijvoorbeeld Kleur of 0 zijn. Als de query meerdere kolommen retourneert en u de waarden in alle kolommen wilt openen, moet u elke kolom verbinden met een andere variabele. Als u kolommen toewijst aan variabelen met behulp van getallen als resultaatsetnamen, geven de getallen de volgorde weer waarin de kolommen verschijnen in de kolomlijst van de query. In de query SELECT Color, ListPrice, FROM Production.Product WHERE ProductID = ?gebruikt u bijvoorbeeld 0 voor de kolom Kleur en 1 voor de kolom ListPrice . De mogelijkheid om een kolomnaam als de naam van een resultatenset te gebruiken, is afhankelijk van de provider die de taak moet gebruiken. Niet alle providers maken kolomnamen beschikbaar.

Sommige query's die één waarde retourneren, bevatten mogelijk geen kolomnamen. De instructie SELECT COUNT (*) FROM Production.Product retourneert bijvoorbeeld geen kolomnaam. U kunt toegang krijgen tot het resultaat met behulp van de rangschikkelijke positie, 0, als resultaatnaam. Voor toegang tot het retourresultaat op kolomnaam moet de query een AS-aliasnaamcomponent <> bevatten om een kolomnaam op te geven. De instructie SELECT COUNT (*)AS CountOfProduct FROM Production.Product, geeft de kolom CountOfProduct . Vervolgens kunt u de kolom met retourresultaten openen met behulp van de kolomnaam CountOfProduct of de rangschikkelijkheid, 0.

Als het resultaatsettype Volledige resultatenset of XML is, moet u 0 gebruiken als de naam van de resultatenset.

Wanneer u een variabele toe wijzen aan een resultatenset met het resultaatsettype Enkele rij , moet de variabele een gegevenstype hebben dat compatibel is met het gegevenstype van de kolom die de resultatenset bevat. Een resultatenset met een kolom met een gegevenstype Tekenreeks kan bijvoorbeeld niet worden toegewezen aan een variabele met een numeriek gegevenstype. Wanneer u de eigenschap TypeConversionMode instelt op Toegestaan, probeert de SQL-taak uitvoeren de uitvoerparameter en queryresultaten te converteren naar het gegevenstype van de variabele waaraan de resultaten zijn toegewezen.

Een XML-resultatenset kan alleen worden toegewezen aan een variabele met het gegevenstype Tekenreeks of Object . Als de variabele het gegevenstype Tekenreeks heeft, retourneert de SQL-taak Uitvoeren een tekenreeks en kan de XML-bron de XML-gegevens gebruiken. Als de variabele het gegevenstype Object heeft, retourneert de SQL-taak Uitvoeren een DOM-object (Document Object Model).

Een volledige resultatenset moet worden toegewezen aan een variabele van het gegevenstype Object . Het resultaat is een rijsetobject. U kunt een Foreach Loop-container gebruiken om de tabelrijwaarden op te halen die zijn opgeslagen in de objectvariabele in pakketvariabelen en vervolgens een scripttaak gebruiken om de gegevens die zijn opgeslagen in pakketvariabelen naar een bestand te schrijven. Voor een demonstratie over hoe u dit doet met behulp van een Foreach Loop-container en een scripttaak.

De volgende tabel bevat een overzicht van de gegevenstypen van variabelen die kunnen worden toegewezen aan resultatensets.

Type resultatenset Gegevenstype van variabele Type van object
Eén rij Elk type dat compatibel is met de typekolom in de resultatenset. Niet van toepassing
Volledige resultaatset Object Als voor de taak een systeemeigen verbindingsbeheer wordt gebruikt, waaronder de ADO-, OLE DB-, Excel- en ODBC-verbindingsbeheerders, is het geretourneerde object een ADO-recordset.

Als de taak gebruikmaakt van een beheerd verbindingsbeheer, zoals het ADO.NET verbindingsbeheer, is het geretourneerde object een System.Data.DataSet.

U kunt een scripttaak gebruiken om toegang te krijgen tot het object System.Data.DataSet , zoals wordt weergegeven in het volgende voorbeeld.

Dim dt As Data.DataTable

Dim ds As Data.DataSet = CType(Dts.Variables("Recordset").Value, DataSet) dt = ds.Tables(0)
XML String String
XML Object Als voor de taak een systeemeigen verbindingsbeheer wordt gebruikt, waaronder de ADO-, OLE DB-, Excel- en ODBC-verbindingsmanagers, is het geretourneerde object een MSXML6. IXMLDOMDocument.

Als de taak gebruikmaakt van een beheerd verbindingsbeheer, zoals het ADO.NET verbindingsbeheer, is het geretourneerde object een System.Xml.XmlDocument.

De variabele kan worden gedefinieerd in het bereik van het uitvoeren van de SQL-taak of het pakket. Als de variabele pakketreikwijdte heeft, is de resultatenset beschikbaar voor andere taken en containers binnen het pakket, en is deze beschikbaar voor alle pakketten die worden uitgevoerd via de taken Uitvoeren van pakket of Uitvoeren van DTS 2000-pakket.

Wanneer u een variabele toewijst aan een resultatenset met één rij, worden niet-tekenreeks waarden die door de SQL-instructie teruggegeven worden, geconverteerd naar tekenreeksen wanneer aan de volgende voorwaarden wordt voldaan:

  • De eigenschap TypeConversionMode is ingesteld op true. U stelt de eigenschapswaarde in het venster Eigenschappen in of met behulp van de SQL-taakeditor uitvoeren.

  • De conversie leidt niet tot afkapping van gegevens.

Resultatensets toewijzen aan variabelen in een SQL-taak uitvoeren

In deze sectie wordt beschreven hoe u een toewijzing maakt tussen een resultset en een variabele in een Execute SQL-taak. Als u een resultatenset toedeelt aan een variabele, wordt de resultatenset beschikbaar voor andere elementen in het pakket. Een script in een scripttaak kan bijvoorbeeld de variabele lezen en vervolgens de waarden uit de resultatenset gebruiken of een XML-bron kan de resultatenset gebruiken die is opgeslagen in een variabele. Als de resultatenset wordt gegenereerd door een bovenliggend pakket, kan de resultatenset beschikbaar worden gesteld aan een onderliggend pakket dat wordt aangeroepen door een execute package-taak door de resultatenset toe te wijden aan een variabele in het bovenliggende pakket en vervolgens een configuratie van een bovenliggende pakketvariabele in het onderliggende pakket te maken om de waarde van de bovenliggende variabele op te slaan.

  1. Open in SQL Server Data Tools (SSDT) het Integration Services-project dat het gewenste pakket bevat.

  2. Dubbelklik in Solution Explorer op het pakket om het te openen.

  3. Klik op het tabblad Controlestroom .

  4. Als het pakket nog geen SQL-taak uitvoeren bevat, voegt u er een toe aan de controlestroom van het pakket. Zie Een taak of een container toevoegen of verwijderen in een besturingsstroom voor meer informatie.

  5. Dubbelklik op de SQL-taak Uitvoeren.

  6. Selecteer in het dialoogvenster Execute SQL Task Editor op de pagina Algemeen het type Enkele rij, Volledige resultaatset, of XML-resultaatset.

  7. Klik op Resultatenset.

  8. Als u een toewijzing van een resultatenset wilt toevoegen, klikt u op Toevoegen.

  9. Selecteer in de lijst Variabelennaam een variabele of maak een nieuwe variabele. Zie Toevoegen, Verwijderen en het bereik van door de gebruiker gedefinieerde variabelen in een pakket veranderen voor meer informatie.

  10. Wijzig eventueel de naam van de resultatenset in de lijst Resultaatnaam .

    Over het algemeen kunt u de kolomnaam gebruiken als de naam van de resultatenset, of u kunt de rangtelpositie van de kolom in de kolomlijst gebruiken als resultatenset. De mogelijkheid om een kolomnaam te gebruiken als de naam van de resultatenset, is afhankelijk van de provider die de taak moet gebruiken. Niet alle providers maken kolomnamen beschikbaar.

  11. Klik op OK.

Problemen met het uitvoeren van de SQL-taak oplossen

U kunt de aanroepen die de SQL-taak uitvoeren uitvoert, registreren bij externe gegevensproviders. U kunt deze logboekregistratiefunctie gebruiken om problemen met de SQL-opdrachten op te lossen die door de SQL-taak uitvoeren worden uitgevoerd. Als u de aanroepen wilt registreren die door de SQL-taak uitvoeren worden uitgevoerd bij externe gegevensproviders, schakelt u pakketlogboekregistratie in en selecteert u de diagnostische gebeurtenis op pakketniveau. Zie Hulpprogramma's voor probleemoplossing voor pakketuitvoering voor meer informatie.

Soms retourneert een SQL-opdracht of opgeslagen procedure meerdere resultatensets. Deze resultatensets bevatten niet alleen rijensets die het resultaat zijn van SELECT-query's, maar enkele waarden die het resultaat zijn van fouten van RAISERROR- of PRINT-instructies. Of de taak fouten negeert in resultatensets die optreden na de eerste resultatenset, is afhankelijk van het type verbindingsbeheer dat wordt gebruikt:

  • Wanneer u OLE DB- en ADO-verbindingsbeheerders gebruikt, negeert de taak de resultatensets die plaatsvinden na de eerste resultatenset. Daarom negeert de taak met deze verbindingsbeheerders een fout die wordt geretourneerd door een SQL-opdracht of een opgeslagen procedure wanneer de fout geen deel uitmaakt van de eerste resultatenset.

  • Wanneer u ODBC en ADO.NET verbindingsmanagers gebruikt, negeert de taak geen resultatensets die plaatsvinden na de eerste resultatenset. Met deze verbindingsbeheerders mislukt de taak met een fout wanneer een andere resultatenset dan de eerste resultatenset een fout bevat.

Aangepaste logboekvermeldingen

In de volgende tabel wordt de aangepaste logboekvermelding voor de SQL-taak uitvoeren beschreven. Zie Integration Services (SSIS) Loggingvoor meer informatie.

Logboekvermelding Description
ExecuteSQLExecutingQuery Bevat informatie over de uitvoeringsfasen van de SQL-instructie. Logboekvermeldingen worden geschreven wanneer de taak verbinding met de database verkrijgt, wanneer de taak begint met het voorbereiden van de SQL-instructie en nadat de uitvoering van de SQL-instructie is voltooid. De logboekvermelding voor de voorbereidingsfase bevat de SQL-instructie die door de taak wordt gebruikt.
  • Last updated on