70-463 · Question #29
You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse. You add an Execute SQL task to the control flow. The task must execute a simple INSERT…
The correct answer is D. INSERT INTO dbo.Table (variablevalue) VALUES (?). OLE DB Connection Managers use positional parameter markers represented by '?' in SQL statements, with parameters matched by zero-based ordinal position defined in the Parameter Mapping tab.
Question
You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse. You add an Execute SQL task to the control flow. The task must execute a simple INSERT statement. The task has the following requirements:
- The INSERT statement must use the value of a string package variable.
- The variable name is StringVar.
- The Execute SQL task must use an OLE DB Connection Manager.
In the Parameter Mapping tab of the Execute SQL task, StringVar has been added as the only parameter. You must configure the SQLStatement property of the Execute SQL task. Which SQL statement should you use?
Options
- AINSERT INTO dbo.Table (variablevalue) VALUES ($StringVar)
- BINSERT INTO dbo.Table (variablevalue) VALUES (0)
- CINSERT INTO dbo.Table (variablevalue) VALUES (@0)
- DINSERT INTO dbo.Table (variablevalue) VALUES (?)
How the community answered
(16 responses)- A6% (1)
- D94% (15)
Why each option
OLE DB Connection Managers use positional parameter markers represented by '?' in SQL statements, with parameters matched by zero-based ordinal position defined in the Parameter Mapping tab.
$StringVar is not valid parameter syntax for any SSIS connection manager type and would cause a SQL syntax error at runtime.
Using the literal value 0 does not reference the package variable at all - it inserts the integer zero rather than the runtime value of StringVar.
@0 is not valid OLE DB parameter syntax - named '@' parameters are used with ADO.NET Connection Managers, not OLE DB Connection Managers.
When an Execute SQL task uses an OLE DB Connection Manager, parameters in the SQL statement must be specified as '?' placeholders, and each '?' is matched to the corresponding parameter in the Parameter Mapping tab by its zero-based Parameter Name ordinal (0). This follows the OLE DB standard for parameterized queries, which is distinct from the named '@param' syntax used by ADO.NET connections.
Concept tested: SSIS Execute SQL task OLE DB parameter mapping syntax
Source: https://learn.microsoft.com/en-us/sql/integration-services/control-flow/execute-sql-task
Topics
Community Discussion
No community discussion yet for this question.