Trabajar con parámetros y códigos de retorno en la tarea Ejecutar SQL

Las instrucciones SQL y los procedimientos almacenados suelen usar parámetros de input, parámetros de output entrada y códigos de retorno. En Integration Services, la tarea Ejecutar SQL admite los tipos de parámetros Input, Output y ReturnValue. Utilice el tipo Input para parámetros de entrada, Output para parámetros de salida y ReturnValue para códigos de retorno.

Nota

Solo puede usar parámetros en una tarea Ejecutar SQL si el proveedor de datos los admite.

Los parámetros de los comandos SQL, incluidas las consultas y los procedimientos almacenados, se asignan a las variables definidas por el usuario que se crean dentro del ámbito de la tarea Ejecutar SQL, un contenedor primario o dentro del ámbito del paquete. Los valores de las variables pueden establecerse en tiempo de diseño o rellenarse dinámicamente en tiempo de ejecución. También se pueden asignar parámetros a las variables del sistema. Para obtener más información, vea Variables de Integration Services y Variables del sistema.

Sin embargo, trabajar con parámetros y códigos de retorno en una tarea Ejecutar SQL implica algo más que simplemente saber qué tipos de parámetros admite la tarea y cómo se asignarán. Hay requisitos de uso adicionales e instrucciones que se deben seguir para utilizar correctamente los parámetros y códigos de retorno en la tarea Ejecutar SQL. El resto de este tema abarca estos requisitos de uso e instrucciones:

• Usar nombres de parámetros y marcadores

• Usar parámetros con tipos de datos de fecha y hora

• Usar parámetros en cláusulas WHERE

• Usar parámetros con procedimientos almacenados

• Obtener valores de códigos de retorno

• Configurar parámetros y códigos de retorno en el Editor de la tarea Ejecutar SQL

Usar nombres de parámetros y marcadores

En función del tipo de conexión que utiliza la tarea Ejecutar SQL, la sintaxis del comando SQL usa marcadores de parámetros diferentes. Por ejemplo, el tipo de administrador de conexiones ADO.NET requiere que el comando SQL utilice un marcador de parámetro con el formato @varParameter, mientras que el tipo de conexión OLE DB requiere el signo de interrogación (?) como marcador de parámetro.

Los nombres que puede utilizar como nombres de parámetros en las asignaciones entre variables y parámetros también varían según el tipo de Administrador de conexiones. Por ejemplo, el tipo de Administrador de conexiones ADO.NET utiliza un nombre definido por el usuario con el prefijo @, mientras que el tipo de Administrador de conexiones OLE DB requiere que se utilice el valor numérico de un ordinal basado en 0 como nombre de parámetro.

La tabla siguiente resume los requisitos de los comandos SQL para los tipos de Administrador de conexiones que puede utilizar la tarea Ejecutar SQL.

Tipo de conexión	Marcador de parámetro	Nombre del parámetro	Comando SQL (ejemplo)
ADO	?	Param1, Param2, …	SELECT FirstName, LastName, Title FROM Person.Person WHERE BusinessEntityID = ?
ADO.NET	@<nombre de parámetro>	@<nombre de parámetro>	SELECT FirstName, LastName, Title FROM Person.Person WHERE BusinessEntityID = @parmBusinessEntityID
ODBC	?	1, 2, 3, …	SELECT FirstName, LastName, Title FROM Person.Person WHERE BusinessEntityID = ?
EXCEL y OLE DB	?	0, 1, 2, 3, …	SELECT FirstName, LastName, Title FROM Person.Person WHERE BusinessEntityID = ?

Usar parámetros con ADO.NET y administradores de conexión ADO

Los administradores de conexión ADO y ADO.NET tienen requisitos concretos para los comandos SQL que utilizan parámetros:

• Los administradores de conexión ADO.NET requieren que el comando SQL utilice nombres de parámetros como marcadores de parámetros. Esto significa que las variables se pueden asignar directamente a los parámetros. Por ejemplo, la variable @varName se asigna al parámetro denominado @parName y proporciona un valor al parámetro @parName.

• Los administradores de conexiones ADO requieren que el comando SQL utilice signos de interrogación (?) como marcadores de parámetros. Sin embargo, puede utilizar cualquier nombre definido por el usuario, salvo valores enteros, como nombres de parámetro.

Para proporcionar valores a los parámetros, se asignan variables a los nombres de parámetro. Después, la tarea Ejecutar SQL utiliza el valor ordinal del nombre del parámetro de la lista de parámetros para cargar los valores de las variables en los parámetros.

Usar parámetros con administradores de conexión en EXCEL, ODBC y OLE DB

Los administradores de conexiones Excel, ODBC y OLE DB requieren que el comando SQL utilice signos de interrogación (?) como marcadores de parámetros y valores numéricos basados en 0 o en 1 como nombres de parámetros. Si la tarea Ejecutar SQL utiliza el Administrador de conexiones ODBC, el nombre de parámetro que se asigna al primer parámetro en la consulta se denomina 1; de lo contrario, el parámetro se denomina 0. Para los parámetros siguientes, el valor numérico del nombre de parámetro indica el parámetro en el comando SQL al que se asigna el nombre de parámetro. Por ejemplo, el parámetro denominado 3 se asigna al tercer parámetro, que se representa con un signo de interrogación (?) en el comando SQL.

Para proporcionar valores a los parámetros, se asignan variables a los nombres de parámetros y la tarea Ejecutar SQL utiliza el valor ordinal del nombre del parámetro para cargar valores de variables a parámetros.

En función del proveedor que utiliza el Administrador de conexiones, es posible que no se acepten algunos tipos de datos OLE DB. Por ejemplo, el controlador de Excel reconoce solo un conjunto limitado de tipos de datos. Para obtener más información sobre el comportamiento del proveedor Jet con el controlador de Excel, vea Origen de Excel.

Usar parámetros con administradores de conexión OLE DB

Cuando la tarea Ejecutar SQL utiliza el Administrador de conexiones OLE DB, está disponible la propiedad BypassPrepare de la tarea. Debería establecer esta propiedad en true si la tarea Ejecutar SQL utiliza instrucciones SQL con parámetros.

Cuando se usa un Administrador de conexiones OLE DB, no se pueden utilizar subconsultas con parámetros, ya que la tarea Ejecutar SQL no puede derivar la información de los parámetros a través del proveedor OLE DB. Sin embargo, puede utilizar una expresión para concatenar los valores de los parámetros en la cadena de consulta y establecer la propiedad SqlStatementSource de la tarea.

Usar parámetros con tipos de datos de fecha y hora

Usar parámetros de fecha y hora con administradores de conexión ADO y ADO.NET

Al leer datos de los tipos SQL Server, time y datetimeoffset, una tarea Ejecutar SQL que utiliza el Administrador de conexiones ADO o ADO.NET tiene los requisitos adicionales siguientes:

• Para los datos de tipo time, un administrador de conexiones de ADO.NET requiere que estos datos se almacenen en un parámetro cuyo tipo sea Input o Output, y cuyo tipo de datos sea string.

• Con los datos datetimeoffset, un Administrador de conexiones ADO.NET requiere que estos datos estén almacenados en uno de los parámetros siguientes:

  • Un parámetro cuyo tipo de parámetro es Input y cuyo tipo de datos es string.

  • Un parámetro cuyo tipo de parámetro es Output o ReturnValue, y cuyo tipo de datos es datetimeoffset, string o datetime2. Si selecciona un parámetro cuyo tipo de datos es string o datetime2, Integration Services convierte los datos en string o datetime2.

• Un administrador de conexiones ADO requiere que los datos de tipo time o datetimeoffset estén almacenados en un parámetro cuyo tipo de parámetro sea Input o Output, y cuyo tipo de datos sea adVarWchar.

Para obtener más información sobre los tipos de datos de SQL Server y cómo se asignan a los tipos de datos de Integration Services, vea Tipos de datos (Transact-SQL) y Tipos de datos de Integration Services.

Usar parámetros de fecha y hora con administradores de conexión OLE DB

Al utilizar un Administrador de conexiones OLE DB, una tarea Ejecutar SQL tiene requisitos de almacenamiento concretos para los datos de los tipos SQL Server, date, time, datetime, datetime2 y datetimeoffset. Debe almacenar estos datos en uno de los tipos de parámetros siguientes:

• Un parámetro de entrada del tipo de datos NVARCHAR.

• Un parámetro de salida del tipo de datos adecuado, tal y como se enumera en la tabla siguiente.

  Tipo de parámetro Output	Tipo de datos de fecha
  DBDATE	date
  DBTIME2	time
  DBTIMESTAMP	datetime, datetime2
  DBTIMESTAMPOFFSET	datetimeoffset

Si los datos no están almacenados en el parámetro de entrada o de salida adecuado, se produce un error en el paquete.

Usar parámetros de fecha y hora con administradores de conexión ODBC

Al utilizar un Administrador de conexiones ODBC, una tarea Ejecutar SQL tiene requisitos de almacenamiento concretos para uno de los datos de los tipos SQL Server, date, time, datetime, datetime2 o datetimeoffset. Debe almacenar estos datos en uno de los tipos de parámetros siguientes:

• Un parámetro de input del tipo de datos SQL_WVARCHAR

• Un parámetro output del tipo de datos adecuado, tal y como se enumera en la tabla siguiente.

  Tipo de parámetro de Output	Tipo de datos de fecha
  SQL_DATE	date
  SQL_SS_TIME2	time
  SQL_TYPE_TIMESTAMP O bien SQL_TIMESTAMP	datetime, datetime2
  SQL_SS_TIMESTAMPOFFSET	datetimeoffset

Si los datos no están almacenados en el parámetro de entrada o de salida adecuado, se produce un error en el paquete.

Usar parámetros en cláusulas WHERE

Los comandos SELECT, INSERT, UPDATE y DELETE suelen incluir cláusulas WHERE para especificar filtros que definen las condiciones que debe cumplir cada fila de las tablas de origen con el fin de satisfacer los requisitos de un comando SQL. Los parámetros proporcionan los valores de filtro en las cláusulas WHERE.

Puede utilizar marcadores de parámetros para proporcionar valores de parámetros de forma dinámica. Las reglas para los marcadores y nombres de parámetros que se pueden utilizar en la instrucción SQL dependen del tipo de Administrador de conexiones que utiliza la tarea Ejecutar SQL.

La tabla siguiente enumera ejemplos del comando SELECT por tipo de Administrador de conexiones. Las instrucciones INSERT, UPDATE y DELETE son similares. Los ejemplos utilizan el comando SELECT para devolver los productos de la tabla Product de AdventureWorks2008R2 que tienen un valor de ProductID mayor y menor que los valores especificados por dos parámetros.

Tipo de conexión	Sintaxis de SELECT
EXCEL, ODBC y OLE DB	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

Los ejemplos necesitarán parámetros que tengan los siguientes nombres:

• Los administradores de conexión en EXCEL y OLED DB utilizan los nombres de parámetro 0 y 1. El tipo de conexión ODBC utiliza 1 y 2.

• El tipo de conexión ADO podría utilizar cualquier par de nombres de parámetros, como Param1 y Param2, pero los parámetros deben asignarse por su posición ordinal en la lista de parámetros.

• El tipo de conexión de ADO.NET utiliza los nombres de parámetros @parmMinProductID y @parmMaxProductID.

Usar parámetros con procedimientos almacenados

Los comandos SQL que ejecutan procedimientos almacenados también pueden usar la asignación de parámetros. Las reglas sobre el uso de marcadores y nombres de parámetros dependen del tipo de Administrador de conexiones que utiliza la tarea Ejecutar SQL, del mismo modo que sucede con las consultas con parámetros.

La tabla siguiente enumera ejemplos del comando EXEC por tipo de Administrador de conexiones. Los ejemplos ejecutan el procedimiento almacenado uspGetBillOfMaterials en AdventureWorks2008R2. El procedimiento almacenado utiliza los parámetros @StartProductID y @CheckDate de input.

Tipo de conexión	Sintaxis de EXEC
EXCEL y OLE DB	EXEC uspGetBillOfMaterials ?, ?
ODBC	{call uspGetBillOfMaterials(?, ?)} Para obtener más información sobre la sintaxis de la llamada ODBC, consulte el tema Parámetros de procedimientos, en la Referencia del programador de ODBC de MSDN Library.
ADO	Si IsQueryStoredProcedure se establece en False, EXEC uspGetBillOfMaterials ?, ? Si IsQueryStoredProcedure se establece en True, uspGetBillOfMaterials
ADO.NET	Si IsQueryStoredProcedure se establece en False, EXEC uspGetBillOfMaterials @StartProductID, @CheckDate Si IsQueryStoredProcedure se establece en True, uspGetBillOfMaterials

Para utilizar parámetros de salida, la sintaxis requiere que la palabra clave OUTPUT siga a cada marcador de parámetro. Por ejemplo, la sintaxis del parámetro de salida siguiente es correcta: EXEC myStoredProcedure ? OUTPUT.

Para obtener más información sobre el uso de parámetros de entrada y salida con procedimientos almacenados de Transact-SQL, vea Parámetros (motor de base de datos), Devolver datos mediante parámetros OUTPUT y EXECUTE (Transact-SQL).

Obtener valores de códigos de retorno

Un procedimiento almacenado puede devolver un valor entero, denominado código de retorno, para indicar el estado de ejecución de un procedimiento. Para implementar códigos de retorno en la tarea Ejecutar SQL, debe utilizar los parámetros del tipo ReturnValue.

La tabla siguiente enumera, por tipo de conexión, algunos ejemplos de comandos EXEC que implementan códigos de retorno. Todos los ejemplos utilizan un parámetro de input. Las reglas del uso de marcadores y nombres de parámetros son las mismas para todos los tipos de parámetros: Input, Output y ReturnValue.

Algunas sintaxis no admiten literales en los parámetros. En tal caso, debe proporcionar el valor del parámetro mediante una variable.

Tipo de conexión	Sintaxis de EXEC
EXCEL y OLE DB	EXEC ? = myStoredProcedure 1
ODBC	{? = call myStoredProcedure(1)} Para obtener más información sobre la sintaxis de la llamada ODBC, consulte el tema sobre Parámetros de procedimientos, en la Referencia del programador de ODBC de MSDN Library.
ADO	Si IsQueryStoreProcedure se establece en False, EXEC ? = myStoredProcedure 1 Si IsQueryStoreProcedure se establece en True, myStoredProcedure
ADO.NET	Si IsQueryStoreProcedure se establece en True. myStoredProcedure

En la sintaxis mostrada en la tabla anterior, la tarea Ejecutar SQL utiliza el tipo de origen Entrada directa para ejecutar el procedimiento almacenado. La tarea Ejecutar SQL también puede utilizar el tipo de origen Conexión de archivos para ejecutar un procedimiento almacenado. Con independencia de si la tarea Ejecutar SQL utiliza el tipo de origen Entrada directa o Conexión de archivos, use un parámetro del tipo ReturnValue para implementar el código de retorno. Para obtener más información sobre cómo configurar el tipo de origen de la instrucción SQL que la tarea Ejecutar SQL ejecuta, vea Editor de la tarea Ejecutar SQL (página General).

Para obtener más información sobre el uso de códigos de retorno con procedimientos almacenados de Transact-SQL, vea Devolver datos mediante un código de retorno y RETURN (Transact-SQL).

Configurar parámetros y códigos de retorno en la tarea Ejecutar SQL

Para obtener más información acerca de las propiedades de los parámetros y códigos de devolución que puede establecer en el Diseñador SSIS, haga clic en el tema siguiente:

• Editor de la tarea Ejecutar SQL (página Asignación de parámetros)

Para obtener más información acerca de cómo establecer estas propiedades en el Diseñador SSIS, haga clic en el tema siguiente:

• Cómo establecer las propiedades de tareas o contenedores

Recursos externos

• Entrada de blog, Procedimientos almacenados con parámetros de salida (en inglés), en blogs.msdn.com

• Ejemplo CodePlex, Ejecutar conjuntos de resultados y parámetros de SQL (en inglés), en msftisprodsamples.codeplex.com

Manténgase al día con Integration Services Para obtener las descargas, los artículos, los ejemplos y los vídeos más recientes de Microsoft, así como las soluciones seleccionadas desde la comunidad, visite la página de Integration Services en MSDN o TechNet: Visite la página de Integration Services en MSDN Visite la página de Integration Services en TechNet Para recibir notificaciones automáticas de estas actualizaciones, suscríbase a las fuentes RSS disponibles en la página.

Vea también

Conceptos

Tarea Ejecutar SQL de SSIS

Trabajar con conjuntos de resultados en la tarea Ejecutar SQL