SSIS Using Parameter Names and Markers

Tsql теория > SSIS Using Parameter Names and Markers
18.04.2018 12:04:26


Наиболее часто встречающиеся слова в статье:

[connection] [parameter] [manager] [FirstName] [LastName] [Contact] [ContactID] [command] [requires] [Execute]


Статья:

Depending on the connection type that the Execute SQL task uses, the syntax of the SQL command uses different parameter markers. For example, the ADO.NET connection manager type requires that the SQL command uses a parameter marker in the format @varParameter, whereas OLE DB connection type requires the question mark (?) parameter marker.

The names that you can use as parameter names in the mappings between variables and parameters also vary by connection manager type. For example, the ADO.NET connection manager type uses a user-defined name with a @ prefix, whereas the OLE DB connection manager type requires that you use the numeric value of a 0-based ordinal as the parameter name.

The following table summarizes the requirements for SQL commands for the connection manager types that the Execute SQL task can use.

Connection type

Parameter marker

Parameter name

Example SQL command

ADO

?

Param1, Param2, …

SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?

ADO.NET

@<parameter name>

@<parameter name>

SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = @parmContactID

ODBC

?

1, 2, 3, …

SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?

EXCEL and OLE DB

?

0, 1, 2, 3, …

SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?