Praca z parametry i Zwróć kody w wykonać zadań SQL
Instrukcji SQL i procedury przechowywane często używane. input Parametry, output Parametry, a kody powrotu. W Integration Services, obsługuje zadanie wykonać instrukcji SQL Input, Output, a ReturnValue typy parametrów. Używasz Input Typ dla parametrów wejściowych Output dla parametrów wyjściowych i ReturnValue dla kodów zwrotnych.
Uwaga
Parametry zadania programu wykonać instrukcji SQL można używać tylko wtedy, gdy dostawca danych obsługuje je.
Parametry w poleceniach SQL, kwerendy i procedury przechowywane są mapowane do zmiennych zdefiniowanej przez użytkownika, które są tworzone w ramach zadania, wykonać instrukcji SQL, kontener nadrzędnego, albo w ramach pakiet.zestaw wartości zmiennych w projekcie czas lub wypełniane dynamicznie przy uruchomieniu czas.Można również mapować parametry do zmiennych systemowych.Aby uzyskać więcej informacji zobacz Integration Services Variables i Zmienne systemowe.
Jednak korzystanie z parametrów i zwracanych kodów w wykonać instrukcji SQL zadanie jest więcej niż obsługuje tylko wiedząc, jaki parametr typy zadania i w jaki sposób mapowania tych parametrów.Brak użycia dodatkowych wymagań i wskazówek pomyślnie używane są parametry i zwracają kodów do wykonywania zadania, wykonać instrukcji SQL.Pozostała część tego tematu omówiono tych wymagań użycia i wskazówek:
Za pomocą nazw parametrów i znaczników
Przy użyciu parametrów z typami danych data i godziny
Używanie parametrów w klauzulach WHERE
Przy użyciu parametrów z procedur przechowywanych
Pobieranie wartości zwracanych kodów
Konfigurowanie parametrów i zwracanych kodów w wykonać zadań edytorze języka SQL
Za pomocą parametru nazwy i znaczniki
W zależności od typu połączenia używającego zadanie wykonać instrukcji SQL Składnia polecenia SQL używa innego parametru.Na przykład ADO.NET Typ menedżer połączeń wymaga polecenia SQL używany znacznik parametru w formacie @ varParameter, typ połączenia DB OLE wymaga znacznik parametru znak zapytania (?).
Nazwy, które można użyć jako nazwy parametrów mapowania między zmiennych i parametrów różnią się także według typów menedżer połączeń.Na przykład ADO.NET Typ menedżer połączeń używa nazwy użytkownika z @ prefiksu, typ Menedżera połączenia OLE DB wymaga, aby użyć wartość numeryczna liczba porządkowa oparte na 0 jako nazwę parametru.
Poniższa tabela zawiera podsumowanie wymagania dotyczące polecenia SQL dla typów menedżer połączeń, można użyć zadania wykonać instrukcji SQL.
Typ połączenia |
Znacznik parametru |
Nazwa parametru |
Przykład polecenia SQL |
|---|---|---|---|
ADO |
? |
Param1 Param2... |
SELECT imię, nazwisko, tytuł FROM Person.Contact WHERE ContactID =? |
ADO.NET |
@<Nazwa parametru> |
@<Nazwa parametru> |
SELECT imię, nazwisko, tytuł Z Person.Contact WHERE ContactID = @ parmContactID |
ODBC |
? |
1, 2, 3, … |
SELECT imię, nazwisko, tytuł FROM Person.Contact WHERE ContactID =? |
EXCEL i OLE DB |
? |
0, 1, 2, 3, … |
SELECT imię, nazwisko, tytuł FROM Person.Contact WHERE ContactID =? |
Przy użyciu parametrów z ADO.NET i menedżerowie połączenia ADO
ADO.NET i menedżerowie połączenia ADO są specyficzne wymagania dotyczące polecenia SQL, które są używane są parametry:
ADO.NET Menedżerowie połączenia wymagają, że polecenia SQL za pomocą parametru nazwy jako znaczników parametrów.Oznacza to, że zmienne mogą być mapowane bezpośrednio do parametrów.Na przykład zmienna @varName jest mapowany do parametru o nazwie @parName i udostępnia wartość parametru @parName.
Menedżerowie połączenia ADO wymagają, aby polecenie SQL używały znaku zapytania (?) jako znaczników parametrów.Można jednak użyć dowolną nazwę użytkownika, z wyjątkiem dla wartości całkowitych jako nazw parametrów.
Aby podać wartości parametrów, zmienne są mapowane do nazwy parametru.Następnie zadanie wykonać instrukcji SQL używa wartości porządkowej nazwę parametru listy parametrów załadować wartości z zmiennych parametrów.
Przy użyciu parametrów z EXCEL ODBC i Menedżer połączeń OLE DB
EXCEL ODBC, OLE DB menedżerów połączenia wymagają, aby polecenie SQL używały znaku zapytania (?) jako parametru i wartości liczbowe 0 lub 1 jako nazw parametrów.Jeśli zadanie wykonać instrukcji SQL używa menedżer połączeń ODBC, nazwę parametru, który jest mapowany do pierwszego parametru kwerendy o nazwie 1; w przeciwnym razie parametr o nazwie 0.Dla kolejnych parametrów wartość numeryczna nazwa parametru oznacza, że parametr w poleceniu SQL, który mapuje nazwy parametru.Na przykład parametr o nazwie 3 mapy i trzeciego parametru jest reprezentowany przez trzeciego znaku zapytania (?) w poleceniu SQL.
Aby podać wartości parametrów, zmienne są mapowane do nazwy parametrów i zadanie wykonać instrukcji SQL korzysta z wartości porządkowej nazwę parametru załadować wartości z zmiennych parametrów.
W zależności od dostawca, który korzysta z menedżer połączeń dla niektórych typów danych OLE DB nie jest obsługiwana.Na przykład sterownik program Excel rozpoznaje tylko ograniczony zestaw typów danych.Aby uzyskać więcej informacji o zachowaniu dostawca Jet ze sterownikiem programu Excel zobacz urządzenie urządzenie źródłowewe programu Excel.
Menedżer połączeń baz danych OLE przy użyciu parametrów
Gdy Menedżer połączeń OLE DB używa zadanie wykonać instrukcji SQL BypassPrepare Właściwość zadania jest dostępna. zestaw tej właściwość true Jeśli zadanie wykonać instrukcji SQL używa instrukcji SQL z parametrami.
Korzystając z menedżer połączeń OLE DB, nie można użyć podkwerendy sparametryzowana, ponieważ wykonać zadania SQL nie może uzyskać informacje o parametrach za pośrednictwem dostawca OLE DB.Jednak za pomocą wyrażenie można łączyć wartości parametrów w ciąg kwerendy i zestaw właściwość SqlStatementSource zadania.
Przy użyciu parametrów z typy danych czas i Data
Korzystanie z narzędzia Data i Parametry czas ADO.NET i menedżerowie połączenia ADO
Podczas odczytywania danych z SQL Server typy, time i datetimeoffset, zadania programu wykonać instrukcji SQL, który używa albo ADO.NET lub obiektów ADO, Menedżer połączeń ma następujące dodatkowe wymagania:
Dla time dane, ADO.NET Menedżer połączeń wymaga te dane mają być przechowywane w parametrze, którego typ parametru to Input lub Output, której typem danych, a string.
Dla datetimeoffset dane, ADO.NET Menedżer połączeń wymaga te dane mają być przechowywane w jednym z następujących parametrów:
Parametr, którego typ parametru to Input i której typem danych jest string.
Parametr, którego typ parametru to Output lub ReturnValue, której typem danych, a datetimeoffset, string, lub datetime2. W przypadku wybrania parametru, której typem danych jest albo string lub datetime2, Integration Services Konwertuje ciąg znaków lub datetime2 danych.
Wymaga, aby albo menedżer połączeń ADO time lub datetimeoffset w parametrze, którego typ parametru jest być przechowywane dane Input lub Output, której typem danych, a adVarWchar.
Aby uzyskać więcej informacji na temat SQL Server typy danych i sposób ich mapowania Integration Services typy danych, zobacz Data Types (Transact-SQL) i Typy danych usług integracja.
Korzystanie z narzędzia Data i parametry czas z menedżerów połączenia bazy danych OLE
W przypadku korzystania z menedżer połączeń OLE DB, zadania programu wykonać instrukcji SQL ma wymagania dotyczące określonego magazynu dla danych SQL Server typy danych date, time, datetime, datetime2, a datetimeoffset. Dane te muszą być przechowywane w jednym z następujących typów parametru:
Parametr wejściowy NVARCHAR typu danych.
Parametru wyjściowego z odpowiedniego typu, wymienione w poniższej tabela.
Output Typ parametru
Typ danych Data
DBDATE
date
DBTIME2
time
DBTIMESTAMP
datetime, datetime2
DBTIMESTAMPOFFSET
datetimeoffset
Jeśli dane nie są przechowywane w odpowiednie dane wejściowe lub wyjściowe parametr, pakiet nie powiedzie się.
Korzystanie z narzędzia Data i godzina parametry z menedżerów połączenia ODBC
W przypadku korzystania z zarządcę połączenie ODBC, zadania programu wykonać instrukcji SQL ma wymagania dotyczące określonego magazynu dla danych z jednym z SQL Server typy danych date, time, datetime, datetime2, lub datetimeoffset. Dane te muszą być przechowywane w jednym z następujących typów parametru:
An input parameter of the SQL_WVARCHAR data type
An output parameter with the appropriate data type, as listed in the following tabela.
Output Typ parametru
Typ danych Data
SQL_DATE
date
SQL_SS_TIME2
time
SQL_TYPE_TIMESTAMP
- lub -
SQL_TIMESTAMP
datetime, datetime2
SQL_SS_TIMESTAMPOFFSET
datetimeoffset
Jeśli dane nie są przechowywane w odpowiednie dane wejściowe lub wyjściowe parametr, pakiet nie powiedzie się.
Używanie parametrów w WHERE klauzule
SELECT, INSERT, UPDATE i DELETE poleceń często zawierać klauzul WHERE do określ filtry, które określają warunki poszczególnych wierszy w tabelach źródłowych muszą spełnić w celu zakwalifikowania wysłał polecenie SQL.Parametry zawierają wartości filtru w klauzulach WHERE.
Za pomocą parametru można dynamicznie podać wartości parametrów.Zasady, dla których parametru znaczników i nazwy parametrów mogą być używane w instrukcja języka SQL, zależą od typu menedżer połączeń, który używa wykonać instrukcja języka SQL.
W poniższej tabela podano przykłady polecenie SELECT według typów menedżer połączeń.Instrukcje INSERT, UPDATE i DELETE są podobne.W przykładach używana do zwracania produktów z SELECT Produkt tabela in AdventureWorks mają IDProduktu powyżej i poniżej wartości określonej przez dwa parametry.
Typ połączenia |
Składnia SELECT |
|---|---|
EXCEL, ODBC, OLEDB |
SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ? |
ADO |
SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ? |
ADO.NET |
SELECT* FROM Production.Product WHERE ProductId > @parmMinProductID AND ProductID < @parmMaxProductID |
W przykładach wymaga parametrów, które mają następujące nazwy:
Menedżerowie połączenia EXCEL i OLED DB za pomocą nazw parametrów 0 i 1.Typ połączenia ODBC używa 1 i 2.
Typ połączenia ADO można użyć dowolnego dwóch nazw parametrów, takich jak Param1 i Param2, ale parametrów musi być mapowany przez ich liczba porządkowa pozycji na liście parametrów.
The ADO.NET connection type uses the parameter names @parmMinProductID and @parmMaxProductID.
Przy użyciu parametrów z procedur przechowywanych
Polecenia SQL, które uruchamiają procedur przechowywanych, można użyć parametru mapowania.Reguły dotyczące korzystania z parametru i nazw parametrów, zależy od typu menedżer połączeń, który używa wykonać instrukcji SQL, tak samo, jak zasady kwerend parametrycznych.
W poniższej tabela podano przykłady polecenia EXEC według typów menedżer połączeń.Uruchom w przykładach uspGetBillOfMaterials procedura przechowywana w AdventureWorks. The stored procedure uses the @StartProductID and @CheckDateinput parameters.
Typ połączenia |
Składnia EXEC |
|---|---|
EXCEL i OLEDB |
EXEC uspGetBillOfMaterials ?, ? |
ODBC |
{call uspGetBillOfMaterials(?, ?)} Aby uzyskać więcej informacji na temat składni połączenie ODBC zobacz temat Parametry procedury, w dokumencie ODBC Programmer's Reference w bibliotece MSDN. |
ADO |
Jeśli IsQueryStoredProcedure jest ustawiona na False, EXEC uspGetBillOfMaterials ?, ? Jeśli IsQueryStoredProcedure jest ustawiona na True, uspGetBillOfMaterials |
ADO.NET |
Jeśli IsQueryStoredProcedure jest ustawiona na False, EXEC uspGetBillOfMaterials @StartProductID, @CheckDate Jeśli IsQueryStoredProcedure jest ustawiona na True, uspGetBillOfMaterials |
Aby korzystać z parametrów wyjściowych, składnia wymaga od słowa kluczowego wyjście każdego znacznika danych parametru.Na przykład następująca składnia parametru wyjściowego jest poprawna: EXEC myStoredProcedure ? OUTPUT.
Aby uzyskać więcej informacji na temat parametrów wejściowych i wyjściowych przy użyciu procedury przechowywanej języka Transact-SQL zobacz Parameters (Database Engine), Zwracanie danych przy użyciu parametrów wyjście, a EXECUTE (Transact-SQL).
Pobieranie wartości kodów zwrotu
Procedura przechowywana może zwracać wartość całkowitą, o nazwie kod powrotny, stanie wykonanie procedury.Aby zaimplementować kody powrotne w zadanie wykonać instrukcji SQL, można używać parametrów ReturnValue Typ.
Poniższa lista zawiera według typu połączenia przykłady poleceń EXEC implementujące kody powrotu.We wszystkich przykładach użyto input parametr. Zasady dotyczące używania parametru i nazwy parametrów są takie same dla wszystkich typów parametrów —Input, Output, a ReturnValue.
Niektóre składni nie obsługuje parametru literałów.W takim przypadek należy podać wartość parametru za pomocą zmiennej.
Typ połączenia |
Składnia EXEC |
|---|---|
EXCEL i OLEDB |
EXEC ? = myStoredProcedure 1 |
ODBC |
{? = call myStoredProcedure(1)} Aby uzyskać więcej informacji na temat składni połączenie ODBC zobacz temat Parametry procedury, w dokumencie ODBC Programmer's Reference w bibliotece MSDN. |
ADO |
Jeśli IsQueryStoreProcedure jest ustawiona na False, EXEC ? = myStoredProcedure 1 Jeśli IsQueryStoreProcedure jest ustawiona na True, myStoredProcedure |
ADO.NET |
Zestaw IsQueryStoreProcedure jest ustawiona na True. myStoredProcedure |
W składni przedstawionej w poprzedniej tabela używa się zadanie wykonać instrukcji SQL Bezpośredniego wprowadzania typu urządzenie źródłowe do uruchomienia procedura przechowywana.Zadanie wykonać instrukcji SQL można również użyć Plik połączenia typu urządzenie źródłowe do uruchomienia procedura przechowywana.Regardlesss czy korzysta zadania SQL Excecute Bezpośredniego wprowadzania or Plik połączenia typu urządzenie źródłowe, użycie, a parametr ReturnValue Wpisz, aby zaimplementować kod zwrotny. Aby uzyskać więcej informacji na temat sposobu konfigurowania typu urządzenie źródłowe w instrukcja języka SQL, który uruchamia zadanie wykonać instrukcja języka SQL zobacz Edytor zadań wykonać SQL (strona głównej).
Aby uzyskać więcej informacji na temat korzystania z języka Transact-SQL kody powrotne procedur przechowywanych, zobacz Zwracanie danych przez przy użyciu kodu zwrotu i RETURN (języka Transact-SQL).
Konfigurowanie parametrów i Zwróć kody w wykonać zadań SQL
Więcej informacji na temat właściwości parametry i powrotu kodami, które zestaw w SSIS Projektant, kliknij następujący temat:
Aby uzyskać więcej informacji o ustawianiu tych właściwości SSIS Projektant, kliknij następujący temat:
Zasoby zewnętrzne
Wpis w blogu, Procedury przechowywane z parametrów wyjściowych, na blogs.msdn.com
Przykładowe CodePlex wykonać parametry SQL i zestawy wyników, na msftisprodsamples.codeplex.com
|
.gif "Integration Services icon (small)")
Konfiguracja w aktualizacji z usług integracja Services pobytu