ssissql-在SSIS中執(zhí)行SQL任務(wù)：輸出參數(shù)與結(jié)果集7935

ssissql_在SSIS中執(zhí)?SQL任務(wù)：輸出參數(shù)與結(jié)果集ssissqlInthepreviousarticle,IgaveanoverviewofExecuteSQLTaskinSSISandweillustratedsomeofthedifferencesbetweenwritinganexpressiontoevaluateSqlStatementSourcepropertyandwritingthisexpressionwithinavariableandchangetheExecuteSQLTaskSourceTypetovariable.在上?篇?章中，我對SSIS中的ExecuteSQLTask進?了概述，并說明了編寫表達式以評估SqlStatementSource屬性與在變量中編寫此表達式并將ExecuteSQLTaskSourceType更改為變量之間的?些區(qū)別。Inthisarticle,IwilldescribethedifferencebetweenusingoutputparametersandresultsetswithinExecuteSQLTaskinSSIS.在本?中，我將描述在SSIS的ExecuteSQLTask中使?輸出參數(shù)和結(jié)果集之間的區(qū)別。NotethatthisarticleisthefiftharticleintheSSISfeaturefacetofaceseries,whichaimstoremoveconfusionandtoillustratesomeofthedifferencesbetweensimilarfeaturesprovidedbySQLServerIntegrationServices.請注意，本?是SSIS功能?對?系列?章中的第五篇，該?章旨在消除混淆并說明SQLServerIntegrationServices提供的相似功能之間的某些區(qū)別。參量(Parameters)ExecuteSQLTaskinSSISallowsusertoexecuteparameterizedSQLstatementandcreatemappingbetweentheseparametersandtheSSISvariables.ToaddaparameterintoaSQLstatementyoumustuseaparametermarkerwhichdiffersbasedontheconnectiontype.SSIS中的執(zhí)?SQL任務(wù)允許?戶執(zhí)?參數(shù)化SQL語句并在這些參數(shù)和SSIS變量之間創(chuàng)建映射。要將參數(shù)添加到SQL語句中，必須使?根據(jù)連接類型?不同的參數(shù)標記。ConnectionTypeADOMarkerExample?Select*fromtablewhereID>?Select*fromtablewhereID>@IDSelect*fromtablewhereID>@IDSelect*fromtablewhereID>?Select*fromtablewhereID>?Select*fromtablewhereID>?ADO.NETSQLMOBILEOLEDB@<parametername>@<parametername>???ODBCEXCEL連接類型阿多記號筆例？從ID>？的表中選擇*。從ID>@ID的表中選擇*從ID>@ID的表中選擇*從ID>？的表中選擇*。從ID>？的表中選擇*。從ID>？的表中選擇*。ADO.NETSQL移動OLEDBODBC@<參數(shù)名稱>@<參數(shù)名稱>？？？卓越TherearethreetypesofparametersthatcanbeusedwithinanExecuteSQLTaskinSSIS:SSIS中的“執(zhí)?SQL任務(wù)”中可以使?三種類型的參數(shù)：1.Inputparameters:usedtopassavalueasaparameterwithinaSQLcommandorstoredprocedure輸?參數(shù)：?于在SQL命令或存儲過程中將值作為參數(shù)傳遞2.Outputparameters:usedtostoreavaluegeneratedfromanSQLcommandorstoredprocedure輸出參數(shù)：?于存儲從SQL命令或存儲過程?成的值3.ReturnValue:usedtostoreavaluereturnedbyanSQLcommandorstoredprocedure返回值：?于存儲由SQL命令或存儲過程返回的值WhenusingExecuteSQLTaskinSSIS,tomapaparametertoavariable,youmustgototheParameterMappingtab,anddefinethevariablemapping.在SSIS中使?“執(zhí)?SQL任務(wù)”時，要將參數(shù)映射到變量，必須轉(zhuǎn)到“參數(shù)映射”選項卡，然后定義變量映射。Asexample,intheExecuteSQLTask,wedefinedthefollowingSQLcommand:例如，在執(zhí)?SQL任務(wù)中，我們定義了以下SQL命令：SELECTFROM*AdventureWorks2017.Sales.SalesPerson(ModifiedDate>=?)WHERENow,ifweclickontheParameterMappingtab,weshouldseethefollowingform:現(xiàn)在，如果單擊“參數(shù)映射”選項卡，則應(yīng)該看到以下形式：Figure1–ParameterMappingTab圖1–參數(shù)映射選項卡Toaddaparametermapping,wemustclickontheAddbutton,sincewehaveonspecifiedoneparameterintheSQLcommandthenweshouldaddonlyonemapping.WhenweclickontheAddbutton,onelineisaddedtothegridasshownintheimagebelow:要添加參數(shù)映射，我們必須單擊“添加”按鈕，因為我們在SQL命令中指定了?個參數(shù)，所以僅應(yīng)添加?個映射。當我們單擊“添加”按鈕時，??將添加到?格中，如下圖所?：Figure2–AddingParameterMapping圖2–添加參數(shù)映射WithinExecuteSQLTaskinSSIS,youhavetoconfigurethefollowingpropertiesofeachparameter:在SSIS中的“執(zhí)?SQL任務(wù)”中，您必須配置每個參數(shù)的以下屬性：VariableName:Selectthevariablenamethatyouwanttomaptoaparameter變量名稱：選擇要映射到參數(shù)的變量名稱Direction:Specifyifthetypeoftheparameter(input,output,returnvalue)?向：指定參數(shù)的類型（輸?，輸出，返回值）DataType:Specifythedatatypeoftheparameter(Itmustbecompatiblewiththedatatypeofthevariable)數(shù)據(jù)類型：指定參數(shù)的數(shù)據(jù)類型（必須與變量的數(shù)據(jù)類型兼容）ParameterName:Thenameoftheparameter,thenamingconventiondependsontheconnectiontype:參數(shù)名稱：參數(shù)名稱，命名約定取決于連接類型：ConnectiontypeADOParameternameParam1,Param2,…@<parametername>1,2,3,…ADO.NETandSQLMOBILEODBCEXCELandOLEDB0,1,2,3,…連接類型參數(shù)名稱阿多參數(shù)1，參數(shù)2...@<參數(shù)名稱>ADO.NET和SQLMOBILEODBC123EXCEL和OLEDB0、1、2、3，...ParameterSize:Specifythelengthoftheparameterwhenusingstringdatatypesotherwiseitmustbe-1(defaultvalue)參數(shù)??：使?字符串數(shù)據(jù)類型時，請指定參數(shù)的長度，否則必須為-1（默認值）輸出參數(shù)(Outputparameters)Whenitcomestooutputparameters,theyareusedtostorevaluesthatcanbesetatanypartoftheSQLcommand,theyhavethesameconceptofthestoredprocedureoutputparameters.Asexample,ifwecanuseasimilarSQLcommandtostoretheMaximumvalueofBusinessEntityIDcolumn:對于輸出參數(shù)，它們?于存儲可以在SQL命令的任何部分設(shè)置的值，它們與存儲過程輸出參數(shù)的概念相同。例如，如果我們可以使?類似SQL命令來存儲BusinessEntityID列的Maximum值：SELECT?=MAX(BusinessEntityID)FROMAdventureWorks2017.Sales.SalesPersonThenwehavetoconfiguretheparametermappingasshownintheimagebelow:然后，我們必須配置參數(shù)映射，如下圖所?：Figure3–OutputParameterexample圖3–輸出參數(shù)?例IfweaddabreakpointonthePostExecuteeventoftheExecuteSQLTask,andwecheckthevariablevalue,itshowstheMaximumBusinessEntityIDvalueasshownbelow:如果我們在ExecuteSQLTask的PostExecute事件上添加?個斷點，并檢查變量值，它將顯?MaximumBusinessEntityID值，如下所?：Figure4–OutputParameterValue圖4–輸出參數(shù)值Inaddition,theoutputparametercanbedefinedinastoredprocedureasthefollowing:此外，可以在存儲過程中按以下?式定義輸出參數(shù)：EXECGetMaxEntityID?OUTPUTYoucanrefertothetolearnmoreaboutExecuteSQLTaskinSSISparametersandreturnvalues.您可以參考以了解有關(guān)SSIS參數(shù)和返回值中的“執(zhí)?SQL任務(wù)”的更多信息。結(jié)果集(ResultSets)WhenusinganExecuteSQLTaskinSSIS,resultsetscanbegeneratedfromanSQLcommandmostlyaSELECTquery.Therearemanytypesofresultsets:在SSIS中使?“執(zhí)?SQL任務(wù)”時，可以從SQL命令（主要是SELECT查詢）?成結(jié)果集。結(jié)果集有很多類型：None:Noresultsetisgenerated?：未?成結(jié)果集SingleRow:Whentheresultsetisasinglerow,suchasSELECTTOP1oraSELECTMAX()commands單?：當結(jié)果集為單?時，例如SELECTTOP1或SELECTMAX（）命令FullResultset:WhentheSQLstatementgeneratesmultiplerowssuchasaSELECT*command完整結(jié)果集：當SQL語句?成多?時（例如SELECT*命令）XML:ThisoptionisusedtostoretheresultwithinanXMLvalueXML：此選項?于將結(jié)果存儲在XML值內(nèi)YoucanselecttheresultsettypefromtheExecuteSQLTaskeditor(GeneralTab):您可以從“執(zhí)?SQL任務(wù)”編輯器（“常規(guī)”選項卡）中選擇結(jié)果集類型：Figure5–ResultSettypeselection圖5–結(jié)果集類型選擇Tostoretheresultsetintoavariable,wemustconfigurethevariablemappingwithintheResultSettab.要將結(jié)果集存儲到變量中，我們必須在“結(jié)果集”選項卡中配置變量映射。Figure6–ResultSetTab圖6–結(jié)果集選項卡IntheResultSettab,youmustspecifytheresultnameandthevariablethatyouwanttomapto.IftheresultsettypeisSinglerow,theresultnamemustbethecolumnnameorthecolumnpositioninthecolumnlist.IftheresultsettypeisFullresultsetorXML,youmustuse0astheresultsetname.在“結(jié)果集”選項卡中，必須指定結(jié)果名稱和要映射到的變量。如果結(jié)果集類型為Singlerow，那么結(jié)果名稱必須是列名稱或列列表中的列位置。如果結(jié)果集類型為完整結(jié)果集或XML，則必須使?0作為結(jié)果集名稱。Whenitcomestovariablemappingeachtypeofresultsetmustbemappedtospecificvariabledatatypes:對于變量映射，必須將每種類型的結(jié)果集映射到特定的變量數(shù)據(jù)類型：SingleRow:Thevariabledatatypedependsonthereturnedcolumndatatype單?：變量數(shù)據(jù)類型取決于返回的列數(shù)據(jù)類型System.ObjectvariableandtheObjecttypeisanSystem.Object變量，并且對象類型主要是ADORecordsetmostlyoraADORecordset，如果是ADO.NET連接，則對象是System.Data.DatasetincaseofADO.NETconnectionSystem.Data.DatasetSystem.StringvariablewheretheresultwillbestoredasanXMLstring,oraSystem.String變量，或者是使?ADO時將結(jié)果集主要存儲為System.ObjectvariablewheretheresultsetisstoredasMSXML6.IXMLDOMDocument或MSXML6.IXMLDOMDocumentmostlyorSystem.Xml.XmlDocument的System.Xml.XmlDocumentwhenusinganADO.NETconnectionSystem.Object變量。NET連接WhenusingSingleRowresultsetorXMLstring,valuesarestoredwithinvariableandcanbeconsumeddirectlywithinExpressions,Tasks,ScriptsorTransformations.But,whentheresultsetisstoredwithinavariableoftypeSystem.Object,thevariablecanbeconsumedusing:使?單?結(jié)果集或XML字符串時，值存儲在變量中，并且可以直接在表達式，任務(wù)，腳本或轉(zhuǎn)換中使?。但是，當結(jié)果集存儲在類型為System.Object的變量中時，可以使?以下?式使?該變量：1.:ThisoptionisusedtoloopovertherowsoftheresultsetandconsumethemrowbyrowbymappingeachrowcolumnswithSSISvariables：此選項?于循環(huán)遍歷結(jié)果集的?，并通過將每個?列與SSIS變量映射來逐?使?它們2.Usinga.NetScript(Task/Component):ThecodediffersbasedontheObjecttype:使?.Net腳本（任務(wù)/組件）：代碼因?qū)ο箢愋?異：1.System.Data.DataSet:System.Data.DataSet：DimdtAsData.DataTableDimdsAsData.DataSet=CType(Dts.Variables("Recordset").Value,DataSet)dt=ds.Tables(0)2.ADORecordSet:ADORecordSet：DimdtAsDataTableDimadapterAsNewOleDbDataAdapter()adapter.Fill(dt,Dts.Variables("User::transactionalRepDBs").Value)3.System.Xml.XmlDocument:System.Xml.XmlDocument：DimxdocAsNewXmlDocument()xdoc.LoadXml(varCollection("User::XmlVariable").Value.ToString())Notethat,anADORecordSetvariable請注意，ADORecordSet變量Foradditionalinformationyoucanrefertotheofficialdocumentations:有關(guān)其他信息，請參閱官??檔：輸出參數(shù)與結(jié)果集(OutputParametersVsResultSets)Manytimes,IwasaskedonthedifferencesbetweenusingoutputparametersandResultSetandwhichismorerecommendedwhenusingExecuteSQLTaskinSSIS.Ingeneral,eachoneoftheseoptionshasitsownusecaseseveniftherearesomesimilarities.Inthissection,Iwilltrytoillustratethedifferencesandsimilaritiesbetweenthesetwooptions.很多時候，有?問我使?輸出參數(shù)和結(jié)果集之間的區(qū)別，在SSIS中使?“執(zhí)?SQL任務(wù)”時，更建議這樣做。通常，即使存在?些相似之處，這些選項中的每?個都有??的?例。在本節(jié)中，我將嘗試說明這兩個選項之間的區(qū)別和相似之處。Whentheresultconsistsofmultiplerows,theoutputparametercannot,sincetheydon’tallowtostoreatablevaluedresults當結(jié)果包含多?時，輸出參數(shù)不能，因為它們不允許存儲表值結(jié)果IfweneedtostorevaluesfromdifferentSQLcommandwecannotuseResultSets,whileoutputparameterscandothetrick:如果需要存儲來?不同SQL命令的值，則不能使?結(jié)果集，?輸出參數(shù)可以解決問題：SELECT?=MAX(ID)FROMEmployeesGOSELECT?=MAX(ID)FROMCustomersIncasethatweneedtoretrieveavaluefromaquerylocatedinthemiddleofthewholeSQLstatementandreusethisvaluewithinthesameSQLstatement,weneedoutputparameters:如果我們需要從位于整個SQL語句中間的查詢中檢索?個值并在同?SQL語句中重?此值，則需要輸出參數(shù)：DECLARE@MaxIDINTSELECT@MaxID=MAX(ID)FROMEmployeesSELECT?=@MAXIDINSERTINTOEMPLOYEES(ID,NAME)VALUE