nerdexam
Microsoft

70-463 · Question #66

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 stat

The correct answer is C. INSERT INTO dbo.Table (variablevalue) VALUES (?). OLE DB Connection Managers use positional question mark placeholders in SQL statements, not named parameters or SSIS variable reference syntax.

Configure and deploy SSIS solutions

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 ($Project::StringVar)
  • CINSERT INTO dbo.Table (variablevalue) VALUES (?)
  • DINSERT INTO dbo.Table (variablevalue) VALUES ($Package::StringVar)

How the community answered

(36 responses)
  • A
    6% (2)
  • B
    6% (2)
  • C
    86% (31)
  • D
    3% (1)

Why each option

OLE DB Connection Managers use positional question mark placeholders in SQL statements, not named parameters or SSIS variable reference syntax.

AINSERT INTO dbo.Table (variablevalue) VALUES (@StringVar)

Named parameter syntax with @ (e.g., @StringVar) is valid for ADO.NET Connection Managers only, not OLE DB Connection Managers.

BINSERT INTO dbo.Table (variablevalue) VALUES ($Project::StringVar)

$Project::StringVar is the SSIS expression syntax for referencing a project-level parameter and is not a valid SQL statement placeholder.

CINSERT INTO dbo.Table (variablevalue) VALUES (?)Correct

When an Execute SQL task uses an OLE DB Connection Manager, parameters in the SQL statement must be positional question mark (?) placeholders. The Parameter Mapping tab maps variables to parameters by ordinal position, so the single mapped StringVar variable supplies the value for the single (?) at runtime.

DINSERT INTO dbo.Table (variablevalue) VALUES ($Package::StringVar)

$Package::StringVar is the SSIS expression syntax for referencing a package variable and cannot be used as a SQL parameter placeholder in a SQL statement.

Concept tested: SSIS Execute SQL task OLE DB positional parameter mapping

Source: https://learn.microsoft.com/en-us/sql/integration-services/control-flow/execute-sql-task

Topics

#Execute SQL task#OLE DB parameter mapping#package variable#parameter syntax

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-463 Practice